I'm working on a time card and have three shifts. The start and end time are
formated to time, the cell with the formula is formated as [h]:mm, the
formula I'm using is
IF(TEXT(E16-D16,"h:mm")"5:00",H16-$G$1,TEXT(E16-D16,"h:mm"))
this works for day's and afternoon shifts but will not work for the midnight
shift from 10:30pm to 7:00am. I'm also calculating two different way's
depending on if the shift is over 5 hours. What formula do I need to use to
cover the midnight shift.

A1 = 11:00 PM
B1 = 7:00 AM

=MOD(B1-A1,1)

Formatted as h:mm returns 8:00

--
Biff
Microsoft Excel MVP


"candros" wrote in message
...
I'm working on a time card and have three shifts. The start and end time
are
formated to time, the cell with the formula is formated as [h]:mm, the
formula I'm using is
IF(TEXT(E16-D16,"h:mm")"5:00",H16-$G$1,TEXT(E16-D16,"h:mm"))
this works for day's and afternoon shifts but will not work for the
midnight
shift from 10:30pm to 7:00am. I'm also calculating two different way's
depending on if the shift is over 5 hours. What formula do I need to use
to
cover the midnight shift.

Without knowing more about this other method you're using for when shift is
more than 5 hrs:

=IF(E16<D16,IF(E16+1-D16TIMEVALUE("5:00"),H16-$G$1,E16+1-D16),IF(E16-D16TIMEVALUE("5:00"),H16-$G$1,E16-D16))

Presumes no one works more than 24 hrs. To adjust for overnight, formula
adds a day (the +1) to end time.

Note that your previous formula actually compared a text value with text,
and ALWAYS returned a false. Also, rather than using the TEXT function to
control final output format, just format the cell to a time format.


--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"candros" wrote:

I'm working on a time card and have three shifts. The start and end time are
formated to time, the cell with the formula is formated as [h]:mm, the
formula I'm using is
IF(TEXT(E16-D16,"h:mm")"5:00",H16-$G$1,TEXT(E16-D16,"h:mm"))
this works for day's and afternoon shifts but will not work for the midnight
shift from 10:30pm to 7:00am. I'm also calculating two different way's
depending on if the shift is over 5 hours. What formula do I need to use to
cover the midnight shift.

Hi,


I have no idea what you have in G1 or H16, you don't tell us but to work
with time over midnight your formula converts to this

=IF((E16-D16+(E16<D16))*245,H16-G1,E16-D16)


It this bit that calculates the difference between 2 times over midnight
E16-D16+(E16<D16)

Mike

"candros" wrote:

I'm working on a time card and have three shifts. The start and end time are
formated to time, the cell with the formula is formated as [h]:mm, the
formula I'm using is
IF(TEXT(E16-D16,"h:mm")"5:00",H16-$G$1,TEXT(E16-D16,"h:mm"))
this works for day's and afternoon shifts but will not work for the midnight
shift from 10:30pm to 7:00am. I'm also calculating two different way's
depending on if the shift is over 5 hours. What formula do I need to use to
cover the midnight shift.

Note that while "6:00" is greater than "5:00" in a text comparison, "13:00"
is less than "5:00", as "1" comes before "5".

Others have given other answers.
--
David Biddulph

"candros" wrote in message
...
I'm working on a time card and have three shifts. The start and end time
are
formated to time, the cell with the formula is formated as [h]:mm, the
formula I'm using is
IF(TEXT(E16-D16,"h:mm")"5:00",H16-$G$1,TEXT(E16-D16,"h:mm"))
this works for day's and afternoon shifts but will not work for the
midnight
shift from 10:30pm to 7:00am. I'm also calculating two different way's
depending on if the shift is over 5 hours. What formula do I need to use
to
cover the midnight shift.