Complete Guide to Excel Time Calculations: Work Hours, Overtime, Breaks, Lateness, Over 24 Hours, and Negative Time
Environment: Excel 365/2019. AI assistance was used to structure and organize this article, but every formula was reproduced and verified directly.
1) Time is a number: 3 core concepts
- Time = a fraction of a day One hour is
1/24, and 30 minutes is1/48. - Display is formatting Calculations use numbers; control the display with
hh:mmor[h]:mm. - Watch for text If a value such as '9:30 is text, convert it with
TIMEVALUE.
2) 12 work-schedule recipes (ready to copy and paste)
| # | Task | Formula | Format |
|---|---|---|---|
| 1 | Total work hours (excluding breaks) | =IF(End | [h]:mm |
| 2 | Crossing midnight (night shift) | =MOD(End-Start,1) - Break | [h]:mm |
| 3 | Convert to decimal hours | =(TotalWork)*24 | 0.00 |
| 4 | Convert to minutes | =ROUND((TotalWork)*24*60,0) | Integer |
| 5 | Hours beyond 9:00 (overtime) | =MAX(0, (TotalWork) - TIME(9,0,0)) | [h]:mm |
| 6 | Automatic break (30 minutes for 6+ hours) | =IF((WorkHours_raw)>=TIME(6,0,0), TIME(0,30,0), 0) | Time |
| 7 | Lateness (based on 09:00) | =MAX(0, Start - TIME(9,0,0)) | [h]:mm |
| 8 | Leaving early (based on 18:00) | =MAX(0, TIME(18,0,0) - End) | [h]:mm |
| 9 | Whether it is a holiday | =IF(OR(WEEKDAY(Date,2)>=6, ISNUMBER(MATCH(Date,Holidays,0))),"Holiday","Work") | General |
| 10 | Holiday hours worked | =IF(Holiday="Holiday", TotalWork, 0) | [h]:mm |
| 11 | Convert text ‘9:30’ to a time | =TIMEVALUE("9:30") | hh:mm |
| 12 | mm:ss → minutes | =MINUTE(A2)+SECOND(A2)/60 | 0.00 |
Managing your data in a Table and using column names (for example, Times[Start]) as structured references makes formulas much safer and easier to read.
3) Separate night work hours only (22:00–06:00)
Normalize the start and end times to the 0–1 range (one day = 1), then find their intersection with the night period.
/* S=Start, E=End, B=Break (in time units) */
=LET(
s, MOD(S,1), e, MOD(E,1), /* normalize to 0–1 */
e2, IF(eSubtract this value from total work hours to get daytime work hours.
4) Round, round up, and round down to 30- or 15-minute increments
// When total work hours are t (a number in days)
Round_30_minutes = MROUND(t*24, 0.5)/24
Round_up_30_minutes = CEILING.MATH(t*24, 0.5)/24
Round_down_30_minutes = FLOOR.MATH(t*24, 0.5)/24
Round_15_minutes = MROUND(t*24, 0.25)/245) Lateness and leaving early: safely display negative time
Displaying negative time with formatting alone has many limitations. Building it as a text string is the safest practical approach.
// Compare the standard time (for example, 09:00) with the actual start time
=LET(d, Start - TIME(9,0,0),
IF(d<0, "-"&TEXT(-d,"[h]:mm"), TEXT(d,"[h]:mm")) )Because accounting and settlement also require a numeric column, use a separate column for =Start - TIME(9,0,0). This two-column approach uses a text display column and a numeric total column.
6) Weekly totals and the 52-hour workweek check
// Get the week number based on a Monday week start
=ISOWEEKNUM(Date)
// Weekly total work hours (assuming a Table: Times[Week], Times[Total])
=LET(w, @Times[Week], SUMIFS(Times[Total], Times[Week], w))
// Warning when hours exceed 52 (text)
=IF(SUMIFS(Times[Total]*24, Times[Week], w) > 52, "Over", "OK")7) Error, formatting, and performance checklist
- [h]:mm format: essential for cumulative times, weekly totals, and monthly totals
- Crossing midnight:
MOD(End-Start,1)orIF(End<Start,End+1,End)-Start - Text times: convert with
TIMEVALUEbefore calculating - Tables and structured references keep formulas safe even when columns move
- Use IFERROR to provide user-friendly messages when input is missing
Management becomes easier when you use these related guides as well—for example, COUNTIFS to summarize the number of late days and overtime days, and TEXTSPLIT to extract only numbers from notes.