Converting into time calculation with differnet dates

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Need help in a query to calculate a time for 2 times that have separate dates..
For Instance

Start Date: 1/12/04 Start Time: 21:3
End Date: 1/13/04 End Time: 01:0

The output I would want from above is either 3:30 hours or 3.5 hours.....I have all the date and time components separated into their own fields...however i can combine if necessary
 
Ive never tried this in access, but cant you add both the Start Date and
start Time into a new Date/Time variable, the same for the End Date and End
Time and perform subtraction on the two variables.

Hope this works


brimil said:
Need help in a query to calculate a time for 2 times that have separate dates...
For Instance:

Start Date: 1/12/04 Start Time: 21:30
End Date: 1/13/04 End Time: 01:00

The output I would want from above is either 3:30 hours or 3.5 hours.....I
Click to expand...
have all the date and time components separated into their own
fields...however i can combine if necessary
 
ElapsedTime:DateDiff("n", [StartDate] & " " & [StartTime], [EndDate] & " " &
[EndTime]) / 60

This will give 3.5.

--
Wayne Morgan
Microsoft Access MVP


brimil said:
Need help in a query to calculate a time for 2 times that have separate dates...
For Instance:

Start Date: 1/12/04 Start Time: 21:30
End Date: 1/13/04 End Time: 01:00

The output I would want from above is either 3:30 hours or 3.5 hours.....I
Click to expand...
have all the date and time components separated into their own
fields...however i can combine if necessary
 
Recombine them into a single date/time variable. There's no real reason to
physically recombine them, you can do it in an expression. Then just
subtract them. Something like this:

= (EndDate + EndTime) - (StartDate + StartTime)

This will only work, however, for an interval less than 24 hours. If your
dates span several days and you want to find the difference in decimal
hours, I have had the best luck storing them as strings and then
manipulating them programmatically.

It's difficult to explain in a short posting, so I have a small sample
database on my website, (see sig below) called "ConvertStringStuff.mdb"
which illustrates how to do this.

--
--Roger Carlson
www.rogersaccesslibrary.com
Reply to: Roger dot Carlson at Spectrum-Health dot Org


brimil said:
Need help in a query to calculate a time for 2 times that have separate dates...
For Instance:

Start Date: 1/12/04 Start Time: 21:30
End Date: 1/13/04 End Time: 01:00

The output I would want from above is either 3:30 hours or 3.5 hours.....I
Click to expand...
have all the date and time components separated into their own
fields...however i can combine if necessary
 
Back
Top