I'm trying to use excel to track my work hours in Military time...however, if my time on duty is greater (in value) than my off duty time, the calculation is boogered up. Example.. I'm on duty at 22:00, and off at 06:00, the time worked is always added, no matter the operator I use, + or -. I've done some forum searching but none seem to address this specific issue.

Could you please be more explicit?
What are you trying to achieve?
Perhaps post a small ampls sheet ( no pics please) with some data and expected results?

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Column b has my time into work as military time

Column C has my time off also as military time.

Column D has total on duty hours. 1900 0600 25.00
However, if I report for work at 1900 hours, and leave at 0600, the calculation adds the time no matter.

I'm not sure about this military time stuff - is it possible to format Excel cell containing date or time as military time (format "hhmm"), or it is really an integer value, but anyway having only time registered (without date part) will make any calculations with those times (on and off) a headache!

Let's assume you have columns OnTime and OffTime formatted as time (I'll leave military part off now - so "hh:mm" - i.e. without date part).

When both OnTime and OffTime are always on same date, calculating working time is easy:
WorkTime = OffTime - OnTime. Like 16:00 - 08:00 = 08:00.

When OnTime is on one date, and OffTime is on next date, and working time is less than 24 hours (like OnTime = 16:00 and OffTime = 02:00), the formula for WorkTime must check which time has greater value. When OffTime is greater, then WorkTime = OnTime - OffTime. When OnTime is greater, then WorkTime = 24:00 - OnTime + OffTime.

Thins get more messy, when working time can be more than 24 hours. Like OnTime = 16:00 on first day, and OffTime = 18:00 on next day. Having only time registered, you have no way to check, were you off on same day or on next one.

The only way to make the calculation foolproof, is to include date part too (NB! You can format both columns to display only time, but you have to enter date and time!). Like
OnTime = 26.07.2022 16:00, OffTime = 27.07.2022 18:00. Column WorkTime must be formatted as "[h]:mm" (NB! Brackets are essential here!). The formula for WorkTime will be simply WorkTime = OffTime - OnTime, and will return 26:00 (This format displays times over 24 hours. Any other time format displays 02:00!).

When you can format datetime columns as military time, the last formula will work too. When you can't - then maybe someone can give better advice!

Example :


On duty ... 20:00 hrs; Off duty ... 01:00. Paste formula in D2, copy down column.

Attached Files

How Many Hours EvenPastMidnight.xlsx (9.9 KB, 4 views)

You should include the day/date portion of the values when calculating elapsed time.
As far as "Military Time" that is just a formatting thing that has noting to due with the calculation.
Most people seem to want to enter the Time separate from the date. You can enter the the current Date and Time using the keystrokes:
CTRL+: CTRL+;
and then edit if you're entering from a separate record.

Post a sample sheet please( no pics)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

In case the date part is always left out, and calculated time interval will always be less then 24 hours, the formula like below will work
Btw, when you enter a time value without date part, you really are entering a datetime value like 00.01.1900 19:00 (what essentially is a time value from day 31.12.1899)!