News & Resources

rss feed


How to Generate Random Dates and Times in Excel

Posted by Nick Gaydos on 01 Dec 2009 has a few great functions for generating random numbers. but it has nothing as simple as RAND() built in to handle dates and times.

Here are a few ways of getting around it.
  • Generate a random date between two dates (A1 and B1):
    =TEXT($A$1+INT(RAND()*($B$1-$A$1)),"mm/dd/yyyy hh:mm:ss")

  • Generate a random time:

    note: Time is just a percentage of 1, so just generate a number between 0 and 1, then convert it to a time format.


  • Put date (A3) and time (C3) together, but force the formatting:
    =TEXT(A3+C3,"mm/dd/yyyy hh:mm:ss")

280 Mill Street Suite A
Rochester, Michigan 48307