-1

What is the formula for calculating a random time that ends later than the start time calculated in another cell?

For example, A1 contains the formula =RANDBETWEEN(1,1359)/60 formatted as a time, so I get a start time between 00:01 and 23:59. I need a formula that will give me an end time that is later than the time in A1.

r2d3
  • 3,554
spydercoll
  • 1

1 Answers1

0

For example, A1 contains the formula =RANDBETWEEN(1,1359)/60 formatted as a time, so I get a start time between 00:01 and 23:59. I need a formula that will give me an end time that is later than the time in A1.

That does not work. Excel stores date and time information as floating point numbers. As opposed to integers, floating point can contain numbers behind the decimal comma.

Each day counts as 1 before the comma, hours, minutes and seconds are calculated are fractions. One hour would be represented as 1/24. One minute would be represented by 1/24/60.

A day has 24*60= 1440 minutes. "23:59" is just a minute less so that is minute number 1439. Therefore your formula would rather be RANDBETWEEN(1,1439). That gives you a random minute between minute number 1 and minute number 1439. To convert that into a fraction corresponding to Excel date and time representation, divide it by the total number of minutes in a day RANDBETWEEN(1,1439)/24/60.

Given your start time in minutes A1=RANDBETWEEN(1,1439) you might calculate the end time as an offset to the start time: A2=RANDBETWEEN(A1,1439)+A1 That will give you a start and end minute of the day. To convert those minutes into Excel date/time format you write: B1=A1/24/60 B2=A2/24/60 and format both as times.

P.S: If you want to be 100% bullet-proof to be compliant with the future version "Excel 2120" you would not use the division above, Microsoft might perhaps change its representation of date and time. Instead, you would use the Excel time()-function to convert hours, minutes and seconds into a time expression. Combined with your restriction for the second value the calculation would get a little bit more complicated. :)

For infotainment about date and time representation please read:

https://www.joelonsoftware.com/2006/06/16/my-first-billg-review/

r2d3
  • 3,554
  • If the formula doesn't work =RANDBETWEEN(1,1359)/60 (and it does, because when I format the cell for time, I get a time between 00:01 and 23:59), then what formula does work? I tried your A2=RANDBETWEEN(A1,1439)+A1 and I still get stop times earlier than the start time. B1=A1/24/60 doesn't work because I don't need a cell to do the /24/60 calculation of another cell; the calculations can all be done in one cell. When I do the A2/24/60, every result ends at 0:00. – spydercoll Jan 10 '21 at 23:05
  • Thank you for letting me know! A2=RANDBETWEEN(A1,1439)+A1 is wrong because RANDBETWEEN(A1,1439) let the number already fall into the right bucket.I was thinking in terms of a standard random generator... Use A2=RANDBETWEEN(A1,1439) – r2d3 Jan 10 '21 at 23:26
  • I figured out the issue. A1=RANDBETWEEN(1,1439)/60 results in an number between 1 and 1439 divided by 60. A2=RANDBETWEEN(A1,1439)/60 results in A1/60 as being the lower range, which is again divided by 60. So if the number generated in A1 is 1, and that is divided by 60, the final result is.016, if the number generated in A2 is .016, that is again divided by 60, resulting in 0.00027. Formatting both for time gives me a time, but A2 is earlier than A1 Additionally, any raw number that is higher than 1, when formatted for time, is calculated as if the time were at least 24 hours later. – spydercoll Jan 12 '21 at 00:38
  • So if A1 is .016 (00:23 when formatted for time) and A2 is 1.016, A2 also formats for time as 00:23. Excel is calculating any number above 1, as far as I can tell as 24 hours + the decimal (formatted as time). – spydercoll Jan 12 '21 at 00:44