Date Calculation

[Cheryl]

I have two fields in a query: "Date Completed" (mm/dd/yyyy) and "Time Used
(hours)" (nn.n). I am trying to calculate a third "Date Started" that I want
in the format mm/dd/yyyy. Assuming an eight-hour day, I am trying to use the
expression [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 convert back to a date again.

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

 

[vanderghast]

One hour = 1/24 of a day, so I would try

[Date completed] - [Time used in hours]/24


but it seems you want consider only 8 hours per day, right? As example, if
you used 10 hours, you want to subtract 2 days? If so, then try rather:


[Date completed] - (1 + [Time used in hours] \ 8)


The operator \ is called the integer division:

? 10 \ 8
1

? 7 \ 8
0


So, indeed, in this case, you need to subtract 1 + timeInHour \ 8
days.


Vanderghast, Access MVP