Date Calculation

[Cheryl]

I have two fields in a query: "Date Completed" (mm/dd/yyyy) and "Time Used
(hours)" (nn.n). I want to calculate a third field "Date Started".
Assuming an eight-hour day, the equation should be
[Date Completed] - [Time Used (hours)]/8
but I can't figure out which Date/Time functions
to use to convert my dates and hours to the same units
to do the calculation and then back to a date in the format mm/dd/yyyy again.

I am using Access 2007 and am not too competent with it. Thank you in
advance for your help.

 

[vanderghast]

A date_time is a data type, like a floating point number can be.

The FORMAT is only superficial. You can have mm/dd/yyyy or something else
applied to a date_time, that does not change the VALUE itself, exactly as
changing the decimal dot to a coma, or writing negative number between
parenthesis, (4), instead with a minus sign, -4, does not change the value
from which the format will be produced. The result of a format is a STRING
(not a date, not a floating point number, not an integer, ... )


A date_time is stored as a floating point point number, with 0 being the
30th of December 1899, midnight. Each integer represent a full day, so the
first of January 1900, at midnight is 2.00000. Time is represented in
decimal portion of a day, so 6 AM is 1/4 of a day and 2.25, as a date, is
thus the first of January 1900 at 6 AM.

You can force a floating point number to become a date with CDate. In the
Immediate (Debug) window, try:

? CDate(2.25)
-- result depends on your regional setting, but should represent the first
of January 1900, at 6AM.


If the dates are all after the first of January 1900, you can use simple
arithmetic:


? Now() - 7
-- same time of the day, 7 days ago

? Now() + 1/24
-- one hour from now

or you can use DateAdd:

? DateAdd("h", -2, now() )
-- two hour, 'h', before (2 is negative), from now.

You can force special format with Format:

? Format( Now( ), "mmmm")
-- the full name of the actual month

Or use special function, such as DatePart, DateSerial, etc.


Vanderghast, Access MVP