Time Calculation Over Midnite -- Where?

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

Guest

I have a Form that uses a Query that calculates the date difference between
the Start Time on Duty and the End Time on Duty. My start and end times are
entered as military time, and the result shows in military time.

My query Calculated field is:

TotalHoursOnDuty: DateDiff("n",[StartTimeOnDuty],[EndTimeOnDuty])

This query field has a format property of FIXED.

My Form Text Box Control shows:

=[TotalHoursOnDUty]\60 & Format([TotalHoursOnDuty] Mod 60,"\:00")

The text box has a format property of SHORT TIME.

These statements are fine for hours in the same day. However, when I enter a
start time of 2300 and and end time of 0700, the result should be 8 hours on
duty. But the result showing in the query is -960.00 and -16:00 on the form.

I know there is a logic statement you can use about calculationg time
difference across midnight. It is:

Format([StartTimeOnDuty] -1 -[EndTimeOnDuty], "Short Time")

My problem is I can't figure out WHERE to use this format statment. Does it
go in the query where the result is the numeric value? Or does it go in the
form where I have changed the numeric value to a time format? Do I add it in
a Format Property Field or the Control Source on the Form?

I have tried putting this several places, but Access interprets the
statement wierdly. I need help getting the statement into the correct
location.

Or .... am I going about this incorrectly???

Thank you for your help with my delima.
 
Easiest way is to use the date with the time, and then the DateDiff function
works just fine.

TotalHoursOnDuty:
DateDiff("n",[StartDate]+[StartTimeOnDuty],[EndDate]+[EndTimeOnDuty])
 
Ken,

I changed the query calculated field to the statement you provided, but now
a paramater box comes up asking for "Start Time" and "End Time". The
original query calculated field uses table fields where the start and end
times on duty are input.

I'm still confused. How does this help me get the midnite problem solved?

I know you are busy, but I'm just not getting through this yet.

Thanks again!
 
Santara,

You need to change the names of the fields inside Kens brackets [] to the
name of the fields in your database. What he gave you was an example.

When you identify that your data type is Date/Time, all you are really doing
is telling Access how to format it. Access stores dates/times as a double
precision number. The integer portion is to the left of the decimal point
and gives the number of days since some date I cannot remember. The decimal
portion gives the percentage of the day that has passed. The example below
shows this for todays date at 6 PM.

?cdbl(#7/13/05 18:00#)
38546.75

When you just enter a time into a field (6:00 pm), then Access just stores
the value of .75. If you enter 6:00 AM, Access stores .25. When you use
datediff("n", .75, .25), Access returns -.5

However if you enter the date and the time you would be storing 38546.75 and
38547.25, and Access will return .5 (12 hours).

Hope this helps you better understand why Ken recommended using the Date
plus the time.


Santara said:
Ken,

I changed the query calculated field to the statement you provided, but now
a paramater box comes up asking for "Start Time" and "End Time". The
original query calculated field uses table fields where the start and end
times on duty are input.

I'm still confused. How does this help me get the midnite problem solved?

I know you are busy, but I'm just not getting through this yet.

Thanks again!




Ken Snell said:
Easiest way is to use the date with the time, and then the DateDiff function
works just fine.

TotalHoursOnDuty:
DateDiff("n",[StartDate]+[StartTimeOnDuty],[EndDate]+[EndTimeOnDuty])
Click to expand...
Click to expand...
 
Dale,

Okay, I see what he and you mean.

However, I don't have a "Start Date" field AND an "End Date" field. I only
have ONE FIELD called WorkDate. They are only entering the Work Date for the
day that the most hours are worked. In other words...if they start at 11:00
pm on Monday and get off on Tuesday at 7:00 am, the work date would be
Tuesday date. To complicate this, if they start at 6:00 pm on Monday and
get off on Tuesday at 2:00 am, the work date would be Monday's date.
Whichever day they work the most hours in is the date input into the WorkDate
field.

I input the date difference formula using just the WorkDate field for both
the Start Time and End Time, but that gives me the same problem.

Is there a way to make the "End Time On Duty" portion force the WorkDate
field to change to one more day forward. I'm not certain that is the best
way to say that... so let's try it this way too. If I use the same WorkDate
field for both portions of the Start Time in the formula, can the second
WorkDate be forced to the next date?

Thanks again!


Dale Fye said:
Santara,

You need to change the names of the fields inside Kens brackets [] to the
name of the fields in your database. What he gave you was an example.

When you identify that your data type is Date/Time, all you are really doing
is telling Access how to format it. Access stores dates/times as a double
precision number. The integer portion is to the left of the decimal point
and gives the number of days since some date I cannot remember. The decimal
portion gives the percentage of the day that has passed. The example below
shows this for todays date at 6 PM.

?cdbl(#7/13/05 18:00#)
38546.75

When you just enter a time into a field (6:00 pm), then Access just stores
the value of .75. If you enter 6:00 AM, Access stores .25. When you use
datediff("n", .75, .25), Access returns -.5

However if you enter the date and the time you would be storing 38546.75 and
38547.25, and Access will return .5 (12 hours).

Hope this helps you better understand why Ken recommended using the Date
plus the time.


Santara said:
Ken,

I changed the query calculated field to the statement you provided, but now
a paramater box comes up asking for "Start Time" and "End Time". The
original query calculated field uses table fields where the start and end
times on duty are input.

I'm still confused. How does this help me get the midnite problem solved?

I know you are busy, but I'm just not getting through this yet.

Thanks again!




Ken Snell said:
Easiest way is to use the date with the time, and then the DateDiff function
works just fine.

TotalHoursOnDuty:
DateDiff("n",[StartDate]+[StartTimeOnDuty],[EndDate]+[EndTimeOnDuty])
Click to expand...
Click to expand...
Click to expand...
 
Let's try this based on what you've posted (note that this will "fail" if a
person works the same number of hours on each day, e.g., if a person starts
at 8 pm and ends at 4 am on the next day..you'll need to modify the
expression based on which date you use for WorkDate in this case):

TotalHoursOnDuty:
DateDiff("n",[WorkDate]-IIf(1-[StartTimeOnDuty] >
[EndTimeOnDuty],0,1)+[StartTimeOnDuty], [WorkDate]+IIf(1-[StartTimeOnDuty] >
[EndTimeOnDuty],1,0)+[EndTimeOnDuty])

And in answer to Dale's memory "jog", the integer portion of the Date/Time
value is the number of days since December 30, 1899.

--

Ken Snell
<MS ACCESS MVP>


Santara said:
Dale,

Okay, I see what he and you mean.

However, I don't have a "Start Date" field AND an "End Date" field. I
only
have ONE FIELD called WorkDate. They are only entering the Work Date for
the
day that the most hours are worked. In other words...if they start at
11:00
pm on Monday and get off on Tuesday at 7:00 am, the work date would be
Tuesday date. To complicate this, if they start at 6:00 pm on Monday
and
get off on Tuesday at 2:00 am, the work date would be Monday's date.
Whichever day they work the most hours in is the date input into the
WorkDate
field.

I input the date difference formula using just the WorkDate field for both
the Start Time and End Time, but that gives me the same problem.

Is there a way to make the "End Time On Duty" portion force the WorkDate
field to change to one more day forward. I'm not certain that is the best
way to say that... so let's try it this way too. If I use the same
WorkDate
field for both portions of the Start Time in the formula, can the second
WorkDate be forced to the next date?

Thanks again!


Dale Fye said:
Santara,

You need to change the names of the fields inside Kens brackets [] to the
name of the fields in your database. What he gave you was an example.

When you identify that your data type is Date/Time, all you are really
doing
is telling Access how to format it. Access stores dates/times as a
double
precision number. The integer portion is to the left of the decimal
point
and gives the number of days since some date I cannot remember. The
decimal
portion gives the percentage of the day that has passed. The example
below
shows this for todays date at 6 PM.

?cdbl(#7/13/05 18:00#)
38546.75

When you just enter a time into a field (6:00 pm), then Access just
stores
the value of .75. If you enter 6:00 AM, Access stores .25. When you use
datediff("n", .75, .25), Access returns -.5

However if you enter the date and the time you would be storing 38546.75
and
38547.25, and Access will return .5 (12 hours).

Hope this helps you better understand why Ken recommended using the Date
plus the time.


Santara said:
Ken,

I changed the query calculated field to the statement you provided, but
now
a paramater box comes up asking for "Start Time" and "End Time". The
original query calculated field uses table fields where the start and
end
times on duty are input.

I'm still confused. How does this help me get the midnite problem
solved?

I know you are busy, but I'm just not getting through this yet.

Thanks again!




:

Easiest way is to use the date with the time, and then the DateDiff
function
works just fine.

TotalHoursOnDuty:
DateDiff("n",[StartDate]+[StartTimeOnDuty],[EndDate]+[EndTimeOnDuty])
Click to expand...
Click to expand...
Click to expand...
 
Thank you...Thank you... Thank you!

It worked perfectly.

I undersand the problem with same number of hours worked each day. At this
time, I don't think that will be a problem.

You are a life saver!

Thanks again!

Santara

Ken Snell said:
Let's try this based on what you've posted (note that this will "fail" if a
person works the same number of hours on each day, e.g., if a person starts
at 8 pm and ends at 4 am on the next day..you'll need to modify the
expression based on which date you use for WorkDate in this case):

TotalHoursOnDuty:
DateDiff("n",[WorkDate]-IIf(1-[StartTimeOnDuty] >
[EndTimeOnDuty],0,1)+[StartTimeOnDuty], [WorkDate]+IIf(1-[StartTimeOnDuty] >
[EndTimeOnDuty],1,0)+[EndTimeOnDuty])

And in answer to Dale's memory "jog", the integer portion of the Date/Time
value is the number of days since December 30, 1899.

--

Ken Snell
<MS ACCESS MVP>


Santara said:
Dale,

Okay, I see what he and you mean.

However, I don't have a "Start Date" field AND an "End Date" field. I
only
have ONE FIELD called WorkDate. They are only entering the Work Date for
the
day that the most hours are worked. In other words...if they start at
11:00
pm on Monday and get off on Tuesday at 7:00 am, the work date would be
Tuesday date. To complicate this, if they start at 6:00 pm on Monday
and
get off on Tuesday at 2:00 am, the work date would be Monday's date.
Whichever day they work the most hours in is the date input into the
WorkDate
field.

I input the date difference formula using just the WorkDate field for both
the Start Time and End Time, but that gives me the same problem.

Is there a way to make the "End Time On Duty" portion force the WorkDate
field to change to one more day forward. I'm not certain that is the best
way to say that... so let's try it this way too. If I use the same
WorkDate
field for both portions of the Start Time in the formula, can the second
WorkDate be forced to the next date?

Thanks again!


Dale Fye said:
Santara,

You need to change the names of the fields inside Kens brackets [] to the
name of the fields in your database. What he gave you was an example.

When you identify that your data type is Date/Time, all you are really
doing
is telling Access how to format it. Access stores dates/times as a
double
precision number. The integer portion is to the left of the decimal
point
and gives the number of days since some date I cannot remember. The
decimal
portion gives the percentage of the day that has passed. The example
below
shows this for todays date at 6 PM.

?cdbl(#7/13/05 18:00#)
38546.75

When you just enter a time into a field (6:00 pm), then Access just
stores
the value of .75. If you enter 6:00 AM, Access stores .25. When you use
datediff("n", .75, .25), Access returns -.5

However if you enter the date and the time you would be storing 38546.75
and
38547.25, and Access will return .5 (12 hours).

Hope this helps you better understand why Ken recommended using the Date
plus the time.


:

Ken,

I changed the query calculated field to the statement you provided, but
now
a paramater box comes up asking for "Start Time" and "End Time". The
original query calculated field uses table fields where the start and
end
times on duty are input.

I'm still confused. How does this help me get the midnite problem
solved?

I know you are busy, but I'm just not getting through this yet.

Thanks again!




:

Easiest way is to use the date with the time, and then the DateDiff
function
works just fine.

TotalHoursOnDuty:
DateDiff("n",[StartDate]+[StartTimeOnDuty],[EndDate]+[EndTimeOnDuty])
Click to expand...
Click to expand...
Click to expand...
 
I have code in a Emergency Room Patient Log that was computing length of stay
in access 2000, but we have moved it to a computer witn access 2003. The
code is only working in part. Me.LOS = DateDiff("n", [ATime], [LTime]) works
for times within the same day, the next line
If ME.LOS < "1" Then
ME.LOS = 1440 + (DateDiff("n", [Atime], LTime]))
Doesnt seem to be working. I have changed the plus sign to minus, moved the
+1440 to the end of the line and there is no change so I suspect this line
isn't working. I switched the "1" to "d" and it works dor stays that run
over to the next day, but the line above (times within the same day) gives
me the difference from 1440 ( eg a 20 minute visit yealds a length of stay of
-1420). I tried changing code to what you told Santara, but there is no end
date in the form so that doesn't workThere is one other change in the Form
Field that is bothersome as well. There is a Box for antibiotics that
patients are discharged on. In the older version the name of the medicine
showed up (as referenced in a table), now the index number shows up rather
than the name of the drug. There are several other boxes that represent
tables of the same type within this form that are working fine (showing names
rather than the index key numbers)

Ken Snell said:
Easiest way is to use the date with the time, and then the DateDiff function
works just fine.

TotalHoursOnDuty:
DateDiff("n",[StartDate]+[StartTimeOnDuty],[EndDate]+[EndTimeOnDuty])


--

Ken Snell
<MS ACCESS MVP>

Santara said:
I have a Form that uses a Query that calculates the date difference between
the Start Time on Duty and the End Time on Duty. My start and end times
are
entered as military time, and the result shows in military time.

My query Calculated field is:

TotalHoursOnDuty: DateDiff("n",[StartTimeOnDuty],[EndTimeOnDuty])

This query field has a format property of FIXED.

My Form Text Box Control shows:

=[TotalHoursOnDUty]\60 & Format([TotalHoursOnDuty] Mod 60,"\:00")

The text box has a format property of SHORT TIME.

These statements are fine for hours in the same day. However, when I enter
a
start time of 2300 and and end time of 0700, the result should be 8 hours
on
duty. But the result showing in the query is -960.00 and -16:00 on the
form.

I know there is a logic statement you can use about calculationg time
difference across midnight. It is:

Format([StartTimeOnDuty] -1 -[EndTimeOnDuty], "Short Time")

My problem is I can't figure out WHERE to use this format statment. Does
it
go in the query where the result is the numeric value? Or does it go in
the
form where I have changed the numeric value to a time format? Do I add it
in
a Format Property Field or the Control Source on the Form?

I have tried putting this several places, but Access interprets the
statement wierdly. I need help getting the statement into the correct
location.

Or .... am I going about this incorrectly???

Thank you for your help with my delima.
Click to expand...
Click to expand...
 
If all you have is the time and the time can be on two different days then try
(DateDiff("n",Starttime,Endtime) + 1440) Mod 1440

Your problem with combobox is probably the underlying query for the row source
of the control. As a guess all your combobox controls are displaying text but
the record is really storing the "index number".

You can check the properties of the combobox control. First see if the row
source is returning more than one column. If so and the first column is the
index number, then change the column widths property to 0 for the first column
and see if you now see the name.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I have code in a Emergency Room Patient Log that was computing length of stay
in access 2000, but we have moved it to a computer witn access 2003. The
code is only working in part. Me.LOS = DateDiff("n", [ATime], [LTime]) works
for times within the same day, the next line
If ME.LOS < "1" Then
ME.LOS = 1440 + (DateDiff("n", [Atime], LTime]))
Doesnt seem to be working. I have changed the plus sign to minus, moved the
+1440 to the end of the line and there is no change so I suspect this line
isn't working. I switched the "1" to "d" and it works dor stays that run
over to the next day, but the line above (times within the same day) gives
me the difference from 1440 ( eg a 20 minute visit yealds a length of stay of
-1420). I tried changing code to what you told Santara, but there is no end
date in the form so that doesn't workThere is one other change in the Form
Field that is bothersome as well. There is a Box for antibiotics that
patients are discharged on. In the older version the name of the medicine
showed up (as referenced in a table), now the index number shows up rather
than the name of the drug. There are several other boxes that represent
tables of the same type within this form that are working fine (showing names
rather than the index key numbers)

Ken Snell said:
Easiest way is to use the date with the time, and then the DateDiff function
works just fine.

TotalHoursOnDuty:
DateDiff("n",[StartDate]+[StartTimeOnDuty],[EndDate]+[EndTimeOnDuty])


--

Ken Snell
<MS ACCESS MVP>

Santara said:
I have a Form that uses a Query that calculates the date difference between
the Start Time on Duty and the End Time on Duty. My start and end times
are
entered as military time, and the result shows in military time.

My query Calculated field is:

TotalHoursOnDuty: DateDiff("n",[StartTimeOnDuty],[EndTimeOnDuty])

This query field has a format property of FIXED.

My Form Text Box Control shows:

=[TotalHoursOnDUty]\60 & Format([TotalHoursOnDuty] Mod 60,"\:00")

The text box has a format property of SHORT TIME.

These statements are fine for hours in the same day. However, when I enter
a
start time of 2300 and and end time of 0700, the result should be 8 hours
on
duty. But the result showing in the query is -960.00 and -16:00 on the
form.

I know there is a logic statement you can use about calculationg time
difference across midnight. It is:

Format([StartTimeOnDuty] -1 -[EndTimeOnDuty], "Short Time")

My problem is I can't figure out WHERE to use this format statment. Does
it
go in the query where the result is the numeric value? Or does it go in
the
form where I have changed the numeric value to a time format? Do I add it
in
a Format Property Field or the Control Source on the Form?

I have tried putting this several places, but Access interprets the
statement wierdly. I need help getting the statement into the correct
location.

Or .... am I going about this incorrectly???

Thank you for your help with my delima.
Click to expand...
Click to expand...
Click to expand...
 
I have code in a Emergency Room Patient Log that was computing
length of stay in access 2000, but we have moved it to a computer
witn access 2003. The code is only working in part. Me.LOS =
DateDiff("n", [ATime], [LTime]) works for times within the same
day, the next line If ME.LOS < "1" Then
ME.LOS = 1440 + (DateDiff("n", [Atime], LTime]))
Doesnt seem to be working.
Click to expand...



That is because you are trying to compare a number in LOS to a
literal character "1".

If me.LOS < 1 Then
is the comparison you want, not If me.LOS < "1" Then



I have changed the plus sign to minus,
moved the +1440 to the end of the line and there is no change so I
suspect this line isn't working. I switched the "1" to "d" and it
works dor stays that run over to the next day, but the line above
(times within the same day) gives me the difference from 1440 ( eg
a 20 minute visit yealds a length of stay of -1420). I tried
changing code to what you told Santara, but there is no end date
in the form so that doesn't workThere is one other change in the
Form Field that is bothersome as well.
Click to expand...


There is a Box for
antibiotics that patients are discharged on. In the older version
the name of the medicine showed up (as referenced in a table), now
the index number shows up rather than the name of the drug. There
are several other boxes that represent tables of the same type
within this form that are working fine (showing names rather than
the index key numbers)
Click to expand...

Install the hotfix for Microsoft Office Service Pack 3
http://support.microsoft.com/kb/945674
Ken Snell said:
Easiest way is to use the date with the time, and then the
DateDiff function works just fine.

TotalHoursOnDuty:
DateDiff("n",[StartDate]+[StartTimeOnDuty],[EndDate]+[EndTimeOnDut
y])


--

Ken Snell
<MS ACCESS MVP>

Santara said:
I have a Form that uses a Query that calculates the date
difference between
the Start Time on Duty and the End Time on Duty. My start and
end times are
entered as military time, and the result shows in military
time.

My query Calculated field is:

TotalHoursOnDuty:
DateDiff("n",[StartTimeOnDuty],[EndTimeOnDuty])

This query field has a format property of FIXED.

My Form Text Box Control shows:

=[TotalHoursOnDUty]\60 & Format([TotalHoursOnDuty] Mod
60,"\:00")

The text box has a format property of SHORT TIME.

These statements are fine for hours in the same day. However,
when I enter a
start time of 2300 and and end time of 0700, the result should
be 8 hours on
duty. But the result showing in the query is -960.00 and
-16:00 on the form.

I know there is a logic statement you can use about
calculationg time difference across midnight. It is:

Format([StartTimeOnDuty] -1 -[EndTimeOnDuty], "Short Time")

My problem is I can't figure out WHERE to use this format
statment. Does it
go in the query where the result is the numeric value? Or does
it go in the
form where I have changed the numeric value to a time format?
Do I add it in
a Format Property Field or the Control Source on the Form?

I have tried putting this several places, but Access interprets
the statement wierdly. I need help getting the statement into
the correct location.

Or .... am I going about this incorrectly???

Thank you for your help with my delima.
Click to expand...
Click to expand...
Click to expand...
 
I have calculated the hours between a departure time and a return time for a
trip. Both Depart and Return Times are in the Short Time format. I want to
calculate ManHours by multiplying the number of staff on the trip by the
difference in hours. How do I get my Table, Query, AND Report to show the
ManHours value (in Hours)for times that are longer than 24 hours?
 
I have calculated the hours between a departure time and a return time for a
trip. Both Depart and Return Times are in the Short Time format.
Click to expand...

I'd store both the departure date and time, and return date and time, in the
same field.

If you have a departure time of 11:30PM and a return time of 1:00am, that
could be 90 minutes... or it could be 25 hours and 30 minutes... or it could
be six days and 90 minutes. It's simply ambiguous to store different times.

If you know for a CERTAINTY that the times can never, even ever under any
catastrophic emergency, exceed 24 hours, try

DateDiff("h", [departure time], [end time]) + IIF([departure time] > [end
time], 24, 0)

in your calculation.
 
Back
Top