none
VBA Insert random date RRS feed

  • Question

  • Hello;

    I am trying to write a vba code to insert a random date that is between a certain period which is noted in two cells in excel (begin and end range) and giving only a workday. I want it to enter the date in a certain cell only when another cell on the same row is changed. And it should not change when a different row is changed. Thank you so much.

    Tuesday, June 9, 2015 3:39 PM

All replies

  • Re: VBA Insert random date

    Also posted in Excel for Developers forum.
    Tuesday, June 9, 2015 4:52 PM
  • Well, it's not totally clear to me what you're trying to do, but I can get you part of the way there.  Here's some VBA code to calculate a random date between two other dates. Let's assume that the "Starting Date" is entered in cell A1, the "Ending Date" is entered in cell A2, and the program will write the random date to cell A3:

    Dim dblDateStart As Double  'Store the dates as Double so results can be calculated more easily.
    Dim dblDateEnd As Double
    Dim dblRandomDate As Double
    Dim dblDateDiff As Double

    'Capture the Start and End date
    dblDateStart = Range("A1").Value
    dblDateEnd = Range("A2").Value

    'Calculate the number of days between Start and End Dates
    dblDateDiff = dblDateEnd - dblDateStart + 1

    'Generate the random date
    dblRandomDate = dblDateStart + (dblDateDiff * Rnd())

    'Write out the random date
    Range("A3").Value = Format$(dblRandomDate, "dd-mmm-yy")

    ====================

    * As for the work days, you can use the "WeekDay" function to figure that out.

    * As for checking to see if another cell on the same row is changed, you can use the Worksheet_Change event to determine that.

    Hope this helps,

    Mark

    • Proposed as answer by Marak60 Friday, June 12, 2015 3:17 PM
    Thursday, June 11, 2015 3:07 AM