More Help on Over Midnight Calc

[Guest]

I put a message in asking for help on a time calculation over midnight on
July 13th.

At the time, I "thought" all was great with the caluclation.

However, I'm now seeing that I still have problems.

Here is the calculation that Ken Snell so thoughtfully provided for me:

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

The reason for the extra code is that I have only one Work Date in the
Table. So we needed a way to force the Work Date to be used twice.

Here is a table of data that uses the data and shows the problem in the far
right column. The midnight portion of the formula works. But, now, the rest
of the times that don't go over midnight don't work.

Unit Work Start End Total Hours Format True
ID Date Time Time On Duty Hours Hours
1234 4/2/2005 20:00 2:00 360.00 6:00 6:00
1234 4/5/2005 7:00 15:00 1920.00 32:00 8:00
1234 4/11/2005 9:00 16:30 1890.00 31:30 7:30

I tried another formula (shown below) but it didn't work either.

Can you help me so more?

Santara

 

[Guest]

Opps! I forgot to put in the other formula.

I tried another formula (shown below) but it didn't work either.

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


Santara

 

[Jeff Boyce]

Santara

If I understand correctly, you have a start date, a start time and an end
time. Is there a reason you couldn't modify the table structure to keep a
start date/time and an end date/time? This would allow you to easily
calculate the difference in hours. Access has a date/time field meant for
keeping both together, and this would let you have one less field in your
table.

Good luck

Jeff Boyce
<Access MVP>

 

[Guest]

Jeff,

95% of the work days are on the same day.

They don't want to enter an end date of the same day. So that keeps me from
adding it to the table.

Santara

Santara

If I understand correctly, you have a start date, a start time and an end
time. Is there a reason you couldn't modify the table structure to keep a
start date/time and an end date/time? This would allow you to easily
calculate the difference in hours. Access has a date/time field meant for
keeping both together, and this would let you have one less field in your
table.

Good luck

Jeff Boyce
<Access MVP>

I put a message in asking for help on a time calculation over midnight on
July 13th.

At the time, I "thought" all was great with the caluclation.

However, I'm now seeing that I still have problems.

Here is the calculation that Ken Snell so thoughtfully provided for me:

TotalHoursOnDuty:
DateDiff("n",[DateWorked]-IIf(1-[StartTimeOnDuty]>[EndTimeOnDuty],0,1)+[Star
tTimeOnDuty],[DateWorked]+IIf(1-[StartTimeOnDuty]>[EndTimeOnDuty],1,0)+[EndT
imeOnDuty])

The reason for the extra code is that I have only one Work Date in the
Table. So we needed a way to force the Work Date to be used twice.

Here is a table of data that uses the data and shows the problem in the far
right column. The midnight portion of the formula works. But, now, the rest
of the times that don't go over midnight don't work.

Unit Work Start End Total Hours Format True
ID Date Time Time On Duty Hours Hours
1234 4/2/2005 20:00 2:00 360.00 6:00 6:00
1234 4/5/2005 7:00 15:00 1920.00 32:00 8:00
1234 4/11/2005 9:00 16:30 1890.00 31:30 7:30

I tried another formula (shown below) but it didn't work either.

Can you help me so more?

Santara

 

[John Vinson]

Jeff,

95% of the work days are on the same day.

They don't want to enter an end date of the same day. So that keeps me from
adding it to the table.

Eh?

There should be no need to have the user separately type in a date and
a time. You could easily have the Form fill in today's date if the
user types only a time. Would that help?

John W. Vinson[MVP]

 

[Douglas J. Steele]

Eh?

There should be no need to have the user separately type in a date and
a time. You could easily have the Form fill in today's date if the
user types only a time. Would that help?

In addition to what John and Jeff have said, note that there is no such
thing as a Time field in Access. You don't need to add another field: you
actually can delete one if you store date and time both in the same field,
as Access meant it to be.

 

[Guest]

John (and Jeff and Doug),

I figured a way around it, based on your suggestion.

Instead of having the user input a second date, I used an IIF statement to
calculate the second date based on the times that were entered.

In reference to Doug's post, since the data is entered several days later
than the actual date of work, we used the DateWorked field in additon to the
StartTime and EndTime. By adding the CaluclatedWorkDateEnd field, I was able
to use just the standard formula for DateDiff.

For anyone else that this may help, I have included both formulas.

Formula to create the Calculated Work End Field on Query --
CalcDateWorkEnd:
IIf([EndTimeOnDuty]<[StartTimeOnDuty],[DateWorked]+1,[DateWorked])

Formula to calculate the Total Hours Worked On Duty --
TotalHoursOnDuty:
DateDiff("n",[DateWorked]+[StartTimeOnDuty],[CalcDateWorkEnd]+[EndTimeOnDuty])

Thanks so much for all your help gentlemen!

You guys are tops in my book! I'm learning to so much from you!

Smiles,

Santara