Calculating a 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!
 
J

John Spencer

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculating times 3
Calculate difference in date and time 1
Calculating number of days left ot my birthday 2
Subtracting date and time values 25
How can I convert the date/time format in a SQL query on Access insidea ASP script ? 1
Unable to format Date field in a query 2
Query week to date, month to date, year to date hours 1
Extracting Month From Date 4

Top