Calculating a Date

  • Thread starter Thread starter Cheryl
  • Start date Start date
C

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".
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 back to a date again.

I am using Access 2007 and am not all that competent with it. Thank you in
advance for your help!
 
You can try the following.

DateValue(DateAdd("h",-[Time Used (hours)] * 3),[Date Completed]))

If you are trying to work this out for weekdays only, then you will probably
need something more complex. If persons work on every day of the week then
this should work to give you the starting date.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top