generating random time entries before and after midnight Excel

[osk89]

I am trying to generate random time entries Between two time limits in Excel. the problem i am facing is the generation works fine with the same day time calculation as soon as the time passes midnight, Excel gets confused and give me wrong values values. I need to generate random entries then match it to the closest value it has in the time series given in the sample workbook. Can anyone help me?

https://1drv.ms/x/s!ArYn8TW3_a3d32oXZtCJ9Bd364vO

 

[joeu2004]

At a minimum, change $C$4+RAND()*($C$5-$C$4) to MOD($C$4+RAND()*($C$5-$C$4+($C$5 < $C$4)),1) .

The term (C5 < C4) adds 1 (24 hours) when the "to" time is on the other side of midnight.

But if the random time is "past midnight", it will be 1.xxxxxx instead of 0.xxxxxx. MOD(...) strips the integer part. So MOD(0.xxxxxx,1) is 0.xxxxxx, but MOD(1.xxxxxx,1) is 0.xxxxxx.

However, even if RAND() is about 0.999999999999999 and the random time appears to be 7:00:00 (in C10), note that =MATCH(TIME(7,0,0),C10,0) returns #N/A, indicating no match! The reason is: the random time is infinitesimally less than 7:00:00. You can see that by formatting the random time and 7:00:00 as Number with 15 decimal places.

So it would be prudent to round the random time to the unit of time what the result to be accurate to: minutes or seconds. To wit:

--TEXT(MOD($C$4+RAND()*($C$5-$C$4+($C$5 < $C$4)),1), "h:m")
or
--TEXT(MOD($C$4+RAND()*($C$5-$C$4+($C$5 < $C$4)),1), "h:m:s")

 

[osk89]

@joeu2004

thanks alot. it worked. but i have one problem now (picture attached). when i use vlookup formula to compare the closest time in the time series, all of the values are correct except the random time between 12:00 and 12:15 doesnt show up in the vlookup. i dont know what the problem is. Can you help?

https://1drv.ms/u/s!ArYn8TW3_a3d32w6eG96sGZHJxsN

 

[joeu2004]

The problem is: you have 12:00 AM at the wrong end of the table.

First, the decimal value of 12:00 AM is zero. Since the lookup column must be in ascending order, 12:00 AM (zero) must be first.

Second, many people are not aware that 12:00 AM is the beginning of the day, not the end of the day. So again, it is correct for 12:00 AM to be first in the table.

PS.... I hope you heeded my caution to use --TEXT(...,"h:m:s") to round to seconds (or "h:m" to round to minutes). I cannot tell from the image. (In the future, upload an Excel file so we can see formulas.) I might have made it sound like it is "prudent" only for one instance. But that was intended to be just an example.

 

[osk89]

ahhhh thats why i am having problems displaying 12. Thank you Very much for the help. for the use of use --TEXT(...,"h:m:s"), i didnt need to because i used vlookup function with approximate match and it is displaying all the values correctly.

 

[joeu2004]

for the use of use --TEXT(...,"h:m:s"), i didnt need to because i used vlookup function with approximate match and it is displaying all the values correctly.

That is incorrect. But you'll learn the hard way, when you get surprising results for random times that appear to match the times in your table.

For example, enter 0:29:59.6 into C4 and 0:29:59.9 into C5. With your formats, they will display as 12:30:00 AM.

With the corrected formula in C10 (MOD only, without TEXT, as you wish), the random time appears to be 12:30:00 AM.

But =VLOOKUP(C10,J10:J12,1) returns 12:15:00 AM, when properly formatted.

Aside.... I know that you would only enter times like 0:29:00 and 0:30:00 into C4 and C5. But the point is: the random time can be within the fractional second range. I am just entering fractional seconds into C4 and C5 to force the situation for demonstration purposes, so we don't have to press F9 repeatedly until the intended mistake appears.

PS.... You have Manual calculation mode set in the upload file. I assume you will change it to Automatic calculation mode so that C10 recalculates when C4 and C5 are changed.