Military time pass midnight trouble

welcome to the forum, Jolibee. try:
=IF(A1="","",24*(MOD(TEXT(RIGHT(A1,4),"00\:00")-TEXT(LEFT(A1,4),"00\:00"),1)))

You could add 24 to negative values, which would give you 10 in you example which seems correct
Ie:

Formula:

Please Login or Register  to view this content.

A better way would be to have shift start time and shift end time in different cells, use the whole date/time as the value, and format the cells to only show the times,
then you could simply subtract start from end, with the cell the formula is in formatted as [h]:mm
this works so well because of the way excel stores dates and times, dte is stored to the left of the decimal point (the "integer" part of the date/time) and time is stored to the right (the "decimal" portion) so 12 hours works out to 0.5 for example)
so as an example :
A1= start date/time (formatted as time, 24:00)
B1= end date/time (formatted as time, 24:00)
C1= B1-A1 (formatted as [h]:mm)

Hope this helps

Originally Posted by Jollibee

Hello I have been stuck trying to figure out how to fix this problem. Im doing a timesheet for my workplace and my boss likes to do it in military time.



I do want to emphasize that I want to keep the shift time in 1 column only Im trying to avoid separating the start and end time into their own column.


TYI

Why a single column? You are creating all sorts of unnecessary difficulties for yourself and if you want to use times in other formulae or perhaps pivot tables then you are dead in the water.

Always keep start/finish times in separate columns. It just makes sense and keeps things simple.

So for example if A1 contains 22:00, and B1 08:00, your formula in C1 would be

Formula:

Please Login or Register  to view this content.

Originally Posted by Jollibee

I made a separate column one but my boss did not like it. He wanted to put a full calendar month in one page with employee name, shift, and total hours. So he wanted all that info in each calendar day box without scalling it too small.

Hi,

Do two columns with 12:00 & 22:00 in each really take up that much more room than a single column with 1200-2200? There is after all only character more.

But if that's what your boss wants then he of course knows best

I just know you're adding complexity when it isn't necessary.