Just a quick post today to cover, what appears to be, a simple question.

How can I add a Time value to a Date value?

Static Dates with a Static Time Defined

So say you have a date value (US format – mm/dd/yyyy) of 9/19/2018 and you wanted to append a time value of 7:30 AM, how can this be done?

Well, this is where one has to be aware of a function like TimeSerial(), similar to DateSerial() but for times. Thus, the solution is remarkably simple and would look like

#9/19/2018# + TimeSerial(7,30,0)

Dynamic Dates with a Static Time Defined

Another common use is that we need to build queries/code that uses todays date, using the Date() function, but need the criteria to include a specific time. Well, the same approach can be used in such a scenario as well, and the expression/code would look something like

Date() + TimeSerial(7,30,0)

Another example
Below we want to add the time of 7:30 AM to a date 5 days in the future

DateAdd("d",5,Date()) + TimeSerial(7,30,0)

As you can see, the basic principle can be applied to pretty much any existing code.

What About Working with Fields that Already Include a Time Component?

If we simply try to apply what we learnt above to a date which already included a time component, such as

#9/19/2018 11:15:00 AM# + TimeSerial(7,30,0)

you’ll quickly see that it adds the existing time component of the date/time value with the TimeSerial time component. Not what we’re after!

In this case, we first need to trim off the existing time component, since we want to overwrite it with our static value. Once again, it’s just a case of being aware of the DateValue() function and then the code becomes as simple as

DateValue(#9/19/2018 11:15:00 AM#) + TimeSerial(7,30,0)

or

DateValue(Now()) + TimeSerial(7,30,0)