Randbetween for Time (Minutes)

shai1974

New Member
Joined
Oct 19, 2016
Messages
12
Hi,

I have the following time in minutes 00:09:42 - meaning it is 9 Minutes and 42 seconds.
What is the formula of randbetween that numbers will be around the 9 minutes, lets say I will have randomly 9 minutes, 10 minutes and 11 minutes (but with the seconds as well, so 00:09:48, 00:09:56, 00:10:05 and so on, randomly)?

Thanks,
Shai
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi, one option might be.

=TIME(0,RANDBETWEEN(9,11),RANDBETWEEN(0,59))

EDIT:

Or:
=TIME(0,0,RANDBETWEEN(540,719))

Or:
=RANDBETWEEN(540,719)/86400
 
Last edited:
Upvote 0
Hi, one option might be.

=TIME(0,RANDBETWEEN(9,11),RANDBETWEEN(0,59))

EDIT:

Or:
=TIME(0,0,RANDBETWEEN(540,719))

Or:
=RANDBETWEEN(540,719)/86400


Hi - Thanks!

Please see the image I have attached, neither of the answers provided the result I needed.
I need in a format of "00:10:01" which is 10 min and 01 second
I received using your formulas 10:01 - I am missing "00" before.
 

Attachments

  • 2019-12-12_1202.png
    2019-12-12_1202.png
    65 KB · Views: 55
Upvote 0
Hi, simply apply apply a custom format to the cell that contains the formula of hh:mm:ss
 
Upvote 0
=TIME(0,RANDBETWEEN(9,11),RANDBETWEEN(0,59))
[....] Or:
=TIME(0,0,RANDBETWEEN(540,719))
[....] Or:
=RANDBETWEEN(540,719)/86400

Either of the first two formulas are okay; and the second formula is better, of course.

I would not use the third formula.

Sometimes, it results in a value that is not the same binary representation of the time as a constant. That might cause problems with comparisons, especially VLOOKUP, MATCH, etc.

We cannot see the difference, even when formatting with h:mm:ss.000, because the difference is infinitesimal.

Sometimes, we can see the difference when formatting with Number and 17 decimal places. For example, when RANDBETWEEN returns 605, the first two formulas return 0.00700231481481482 -
-4.34E-18. The third formulas returns 0.00700231481481481 + 4.34E-18.

But sometimes, we cannot see the difference with Number and 17 dp, because Excel formats only the first 15 significant digits (rounded). For example, when RANDBETWEEN returns 577, the first two formulas return 0.00667824074074074 + 1.73E-18. The third formula returns 0.00667824074074074 + 8.67E-19.

Because the results appear to be the same to 15 significant digits, the "=" operator returns TRUE. But MATCH returns #N/A (no match) because of the binary difference.
 
Upvote 0
Sometimes, it results in a value that is not the same binary representation of the time as a constant. That might cause problems with comparisons, especially VLOOKUP, MATCH, etc.

Hi, it's a fair point and I agree that it would be best to use one of the other options.
 
Upvote 0
Sorry deleting my post - was wrong formula
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,064
Members
448,545
Latest member
kj9

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top