How to Generate a random time and date together

[FerociousCamel]

How do you generate a random set of date/time only in the month May of 2013 and between times 8am and 9:59am.

[MrShorty]

I would put enter 1 May 2013 into a cell (after entering it, you could format as general to see the serial number for the date, if you wanted). Then enter 31 May 2013 into a second cell. Then =RANDBETWEEN(cell with 1 May, cell with 31 May) to generate the date serial number.

Time is just a fraction (real number) of a day. The RAND() help file has a formula for generating a random real number between two numbers a and b. Using the formula given in the RAND() function help file (https://support.office.com/en-us/art...0-021ea9f5be73 ), you could enter 08:00:00 into a cell (again, format as general to see the actual fraction of a day, if you want) and 09:59:00 into a second cell, then enter the formula from the RAND() help file =RAND()*(cell with 9:59-cell with 8)+cell with 8. Once you have both parts, simply add them together to get the final random date/time serial number.

If you don't like the cell references, once you have the serial numbers for the dates and times, you can enter those numbers as constants into those formulas.

edit to add: if you are unfamiliar with Excel's date/time serial number system, I recommend something like this to bring yourself up to speed: http://www.cpearson.com/Excel/datetime.htm

[johnmpl]

Hi, to both!

Try this formula:
=RANDBETWEEN(--"2013-05-01",--"2013-05-31")+RAND()*("9:59"-"8:00")+"8:00"

Blessings!