Tricky Date & Time Calucation

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

Guest

I have four fields "Arrival Date" "Arrival Time" "Delivery Date" and "
Delivery Time"
(all using military time)
Here is my question:
How do I figure out the time different between the four fields?
Here is an example (in respectible order)

"01/01/2005" "13:00" "01/02/2005" "13:00" Ans: 1 day 0 hours 0 minutes
"01/01/2005" "13:00" "01/02/2005" "14:05" Ans: 1 day 1 hours 5 minutes
This is tricky (how would this be calucated):
"01/01/2005" "13:00" "01/02/2005" "12:00" Ans: 0 day -1 hours 0 minutes
It should only be (23 hours)

Does anyone have any suggests?
Thank you
 
First of all, assuming your date and time fields are actually text, you can
convert them into a real date/time field in a query like this:

Expr1: CDate([Delivery Date] & " " & [Delivery Time])
Expr2: CDate([Arrival Date] & " " & [Arrival Time])

You can now subtract the two:
Expr3: CDate([Delivery Date] & " " & [Delivery Time])-CDate([Arrival Date] &
" " & [Arrival Time])
(Note: you don't actually have to have Expr1 or Expr2 in your query for
Expr3 to work. They're just for illustration)

However, these will return values as double precision numbers like so:
Arrival Date Arrival Time Delivery Date Delivery Time Expr1
01/01/2005 13:00 01/02/2005 13:00 1
01/01/2005 13:00 01/02/2005 14:05 1.04513888889051
01/01/2005 13:00 01/02/2005 12:00 0.958333333335759


Now you are faced with the task of formatting them they way you want. Do
you actually want them formatted as: X day X hours X minutes? Or is the
decimal equivalent sufficient?


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L
 
Sorry I didn't get back to you, but how would I format into:days, hours,
minutes? thanks


Roger Carlson said:
First of all, assuming your date and time fields are actually text, you can
convert them into a real date/time field in a query like this:

Expr1: CDate([Delivery Date] & " " & [Delivery Time])
Expr2: CDate([Arrival Date] & " " & [Arrival Time])

You can now subtract the two:
Expr3: CDate([Delivery Date] & " " & [Delivery Time])-CDate([Arrival Date] &
" " & [Arrival Time])
(Note: you don't actually have to have Expr1 or Expr2 in your query for
Expr3 to work. They're just for illustration)

However, these will return values as double precision numbers like so:
Arrival Date Arrival Time Delivery Date Delivery Time Expr1
01/01/2005 13:00 01/02/2005 13:00 1
01/01/2005 13:00 01/02/2005 14:05 1.04513888889051
01/01/2005 13:00 01/02/2005 12:00 0.958333333335759


Now you are faced with the task of formatting them they way you want. Do
you actually want them formatted as: X day X hours X minutes? Or is the
decimal equivalent sufficient?


--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L


bladelock said:
I have four fields "Arrival Date" "Arrival Time" "Delivery Date" and "
Delivery Time"
(all using military time)
Here is my question:
How do I figure out the time different between the four fields?
Here is an example (in respectible order)

"01/01/2005" "13:00" "01/02/2005" "13:00" Ans: 1 day 0 hours 0 minutes
"01/01/2005" "13:00" "01/02/2005" "14:05" Ans: 1 day 1 hours 5 minutes
This is tricky (how would this be calucated):
"01/01/2005" "13:00" "01/02/2005" "12:00" Ans: 0 day -1 hours 0 minutes
It should only be (23 hours)

Does anyone have any suggests?
Thank you
Click to expand...
Click to expand...
 
Back
Top