Generating Random time values in Excel

Hello all. Let’s look at how to generate random time values in excel. We shall be using the floor.math function and the rand function to generate time values.

rand() function will generate random value between 0 and 1. Syntax is =rand()

floor.math function rounds off the number to the nearest integer or the provided significance value. Syntax is =floor.math(number, [significance],[mode])

The formula we shall be using this is =floor.math(rand(),”0:15″)

Generating time with floor and rand functions

Please ensure you change the format of cells to time as the output of the formula will be number.

Let’s break the formula:

rand() will generate a value between 0 and 1 as decimal values (like 0.44 or 0.0558 etc). This is the number argument for the floor.math function – in our example 0.058002. Let’s provide the significance argument as 0:15 as we need to round off the time value to multiples of 15 minutes. If you need in multiples of 10 mins, provide the significance argument as 10 & so on.

The list gets refreshed everytime you perform any action in excel or hit the F9 button as shortcut.

Excel deals with date & time in a defined way. For excel 1 = 1 day, 1/6 = 6 hours (i.e.,) 0.25 days. All we need to do is multiply the given number by 24 to get the hours, multiply by 1440 to get minutes and multiply by 86400 to get seconds.

Check here to see how to generate random dates or here to see how to generate random names.

I hope you like this short tutorial. Please help us with your valuable feedback. Thanks for reading!

Leave a Reply

%d bloggers like this: