How do I calculate the difference between 11:00pm and 7:00am



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
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.

Luke M

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


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.

Mike H


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


It this bit that calculates the difference between 2 times over midnight


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question