Calculating elapsed time w/DateDiff - negative minutes in some cal

[Guest]

I am using a DateDiff function in a query to calculated shift length. For
example, in my query, I calculate total time worked on Sunday as:

TotalSunday: DateDiff("n",[SundayStart],[SundayEnd]).

However, if an employee works a shift that begins in the afternoon and ends
early the next morning (ex. 7:00 pm to 3:00 am), the calculation returns a
negative number of minutes -480.

Any solutions for this? Do I have to change my time format to military
time, I'd rather not as I feel it would cause errors in data entry.

Thanks! Any help would be greatly appreciated.

Kate

 

[Allen Browne]

Add one day (i.e. 1440 minutes) if the shift ends before it starts:

TotalSunday: DateDiff("n",[SundayStart],[SundayEnd]) + IIf([SundayEnd] <
[SundayStart], 1440,0)

 

[Guest]

Thanks so much, that definitely did the trick! Now I just have to put in an
input mask to convert minutes to hours and I should be golden.

Kate

Add one day (i.e. 1440 minutes) if the shift ends before it starts:

TotalSunday: DateDiff("n",[SundayStart],[SundayEnd]) + IIf([SundayEnd] <
[SundayStart], 1440,0)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am using a DateDiff function in a query to calculated shift length. For
example, in my query, I calculate total time worked on Sunday as:

TotalSunday: DateDiff("n",[SundayStart],[SundayEnd]).

However, if an employee works a shift that begins in the afternoon and
ends
early the next morning (ex. 7:00 pm to 3:00 am), the calculation returns a
negative number of minutes -480.

Any solutions for this? Do I have to change my time format to military
time, I'd rather not as I feel it would cause errors in data entry.

Thanks! Any help would be greatly appreciated.

Kate

 

[Allen Browne]

This link might give you the clue as to how to display your TotalSunday
field as hours and minutes:
http://allenbrowne.com/casu-13.html

If you are wanting to do any other math on the field (summing, averaging,
etc), it is important to leave in as a number, and do the display in the
text box on your form/report rather than in the query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Thanks so much, that definitely did the trick! Now I just have to put in
an
input mask to convert minutes to hours and I should be golden.

Kate

Add one day (i.e. 1440 minutes) if the shift ends before it starts:

TotalSunday: DateDiff("n",[SundayStart],[SundayEnd]) + IIf([SundayEnd] <
[SundayStart], 1440,0)

I am using a DateDiff function in a query to calculated shift length.
For
example, in my query, I calculate total time worked on Sunday as:

TotalSunday: DateDiff("n",[SundayStart],[SundayEnd]).

However, if an employee works a shift that begins in the afternoon and
ends
early the next morning (ex. 7:00 pm to 3:00 am), the calculation
returns a
negative number of minutes -480.

Any solutions for this? Do I have to change my time format to military
time, I'd rather not as I feel it would cause errors in data entry.