CODEWITHSUNDEEP
vbadatedatetimeoutlookdatetime-format
Matt Bartlett
Matt Bartlett

Reputation: 360

Changing or add the time to a Date in VBA

I am working with a VBA project for Outlook. The project is looking at Calendar appointments and comparing them to dates pulled out of an SQL database and stored in a Date variable.

Currently when the dates come out of the SQL database they not have any Time component to them but I would like to add one to it.

For example out of the SQL query comes "07/03/2018" and I would like to be able to set this as "07/03/2018 09:00:00" and various other times of the day.

Is there a way of doing this without converting the date variable to a string and just adding the time on at the end? This is because I would like to be able to use Date based functions such as DateAdd etc. on the variable later in the program.

Upvotes: 4

Views: 15922

Answers (1)

ashleedawg
ashleedawg

Reputation: 21619

There are a ton of ways to add dates and times together.

Here's a few off the top of my head:

Sub examples()

    'store an example date
    Dim dt As Date
    dt = CDate("March 7, 2018")

    'now to add 9 hours:
    Debug.Print dt + (9 / 24)

    'or, using DateAdd:
    Debug.Print DateAdd("h", 9, dt)

    'or, add a time:
    Dim tm As Date
    tm = CDate("9:00 am")
    Debug.Print dt + tm

    'or, as text:
    Dim str As String
    str = Format(dt, "mmm d, yyyy")
    str = str & " 9:00 am"
    Debug.Print CDate(str)

    'or, with TimeValue:
    Debug.Print dt + TimeValue("9:00")

    'or, get extract the date from a datettime and add a different time:
    Debug.Print DateValue(Now()) + TimeValue("9:00")

End Sub

Which method is best depends on what exactly you're trying to do with it.

Dates are just numbers

In Excel and VBA, dates (or DateTimes) are just numbers starting at:

Midnight on "January 0, 1900"

...and increasing by 1 per day.

1 = January 1, 1900, 00:00:00 (00:00 = midnight)
2 = January 2, 1900, 00:00:00

43166 = March 7, 2018

Need to make it 9:00 AM on Jan 2, 1900?

  • 1 hour is 1/24th of a day.

  • ∴ 9:00 am = (9 ÷ 24) = 0.375

  • ∴ 2.375 = January 2, 1900 9:00 am

Similarly:

  • ∴ 9:00 pm = (18 ÷ 24) = 0.875

  • ∴ 2.875 = January 2, 1900 9:00 pm

    6:00am = 0.25 (¼ day) NOON = 0.5 (½ day) 6:00pm = 0.75 (¾ day)

    1 hour ≈ 0.417 1 minute ≈ 0.000695 1 second ≈ 0.000011574

How many days until Summer?

Today          =   43166  (March 7, 2018)
Summer begins  =   43272  (June 21, 2018)  [Northern Hemisphere]

43272 - 43166  =   106 days

How many hours until Summer?

106 × 24       =   2544 hours

...you get the picture. ☺

Maximum time possible in Excel:

Friday, December 31, 9999 23:59:59  =  2958465.99998843

(...so don't make any plans for that weekend.)

More Information:

Upvotes: 7

Related Questions