To subtract time in Google Sheets, put the later time in one cell and the earlier time in another, then type a formula like =B2-A2 in a third cell and set that cell to Format > Number > Duration. For a shift that ends after midnight, use =MOD(B2-A2,1) instead. These steps cover Google Sheets in a web browser on Windows, Mac and ChromeOS.
What Sheets is actually subtracting
Every date and time in a spreadsheet is a number underneath. The whole-number part counts days since December 30, 1899, and the decimal part is a fraction of one day. Noon is 0.5, 6:00 AM is 0.25, and one hour is 1/24 (about 0.0417). Google documents this in its DATE function reference, which explains that Sheets uses the 1900 date system.
Because of that, subtracting one time from another is ordinary subtraction. The only hard part is getting Sheets to display the answer in a form you can read. Two terms matter here:
- Time format shows a clock reading, such as 5:30:00 PM. Use it for start times and end times.
- Duration format shows a length of time, such as 8:30:00, and keeps counting past 24 hours (for example 36:00:00). Use it for the result of a subtraction.
Before you start, make sure each time is typed with colons and, for a 12-hour clock, an AM or PM after it (for example 8:30 AM). An entry like 8.30 pm is usually stored as text, not a time, and a formula that uses it will fail. If you need a refresher on the basic minus sign, see the basics of subtracting in Google Sheets first.
Find the time between a start and an end
This is the method most people need: how long something took, given when it started and when it finished on the same day.
- Open your spreadsheet at sheets.google.com. In cell A1 type
Start, in B1 typeEnd, and in C1 typeElapsed. - Click A2 and type the start time, for example
9:15 AM. Press Enter. - Click B2 and type the end time, for example
5:45 PM. Press Enter. Sheets recognizes both entries as times and right-aligns them. - Click C2 and type
=B2-A2, then press Enter. Always put the later time first. Depending on the cell’s current format, you may see a decimal such as 0.3541666667 or a clock time such as 8:30:00 AM. Both are the right value in the wrong costume. - With C2 still selected, open the Format menu, point to Number, and click Duration. The cell now reads 8:30:00, meaning 8 hours and 30 minutes.
- To repeat the formula for more rows, click C2, then drag the small square at the bottom-right corner of the cell down the column. Each row subtracts its own start from its own end.
The MINUS function gives the same answer. =MINUS(B2,A2) is equivalent to =B2-A2, as the MINUS function help page states. Most people find the minus sign quicker to type and easier to read, but using the MINUS function in Sheets is worth knowing if you build formulas inside other functions.
Shifts that cross midnight
If someone starts at 10:00 PM and finishes at 6:00 AM, =B2-A2 returns a negative number, because 6:00 AM (0.25) is smaller than 10:00 PM (about 0.9167). You have two dependable fixes. Pick one and use it for the whole column.
Option 1: MOD
Type =MOD(B2-A2,1) and format the cell as Duration. MOD returns the remainder after dividing by 1 (one day), which wraps a negative difference back onto a 24-hour clock. A same-day difference is left alone, so this one formula works for day shifts and night shifts. The syntax is described on the MOD function page.
Option 2: Add a day when the end is earlier
Type =B2-A2+(B2<A2). The comparison (B2<A2) is TRUE when the end time is earlier than the start time, and Sheets treats TRUE as 1 in arithmetic, so one full day is added only for overnight rows. If you prefer something more readable, =IF(B2<A2,B2+1-A2,B2-A2) does the same thing.
Both options assume the shift is shorter than 24 hours. If a span can be longer than a day, record the date along with the time (see the date and time section below).
Take a fixed amount of time away from a time
Sometimes you are not comparing two times but moving one back, such as finding when to start a 4-hour task that is due at 5:00 PM. Use the TIME function, which takes hours, minutes and seconds in that order:
=A2-TIME(4,0,0) subtracts 4 hours
=A2-TIME(0,45,0) subtracts 45 minutes
=A2-TIME(2,31,20) subtracts 2 hours, 31 minutes and 20 seconds
Format the result cell as Time, since the answer is a clock reading. Minutes and seconds above 59 roll over correctly, so TIME(0,90,0) is 1 hour 30 minutes. Hours are different: according to the TIME function documentation, out-of-range values are silently recalculated, and TIME(25,0,0) becomes 1:00 AM. That means TIME can never hold 24 hours or more, and =A2-TIME(27,0,0) takes away only 3 hours.
For amounts of a day or longer, divide by the number of units in a day instead:
=A2-27/24 subtracts 27 hours
=A2-1500/1440 subtracts 1,500 minutes
=A2-90000/86400 subtracts 90,000 seconds
When A2 holds both a date and a time, these formulas move the date back as well, which is usually what you want.
Subtracting full date and time stamps
If each cell holds a date and a time, such as 11/3/2026 2:30 PM minus 11/1/2026 8:30 AM, plain =B2-A2 works and handles any number of days. Format the result as Duration and it reads 54:00:00. For a label that splits out whole days, use =INT(B2-A2)&" days, "&TEXT(MOD(B2-A2,1),"h:mm")&" hrs", which reads “2 days, 6:00 hrs” here. Avoid the d code in a custom number format for this job: d stands for the day of the month, not a count of days elapsed, so it won’t give a reliable day count.
To count whole days between two dates rather than hours, subtracting dates in Google Sheets covers that separately.
Turn the difference into hours, minutes or seconds
Payroll and billing usually need a plain number like 8.5, not 8:30:00. Multiply the difference by the number of units in a day, then set the cell to Format > Number > Number (not Time or Duration, or the result will be displayed as a clock again).
| You want | Formula | 9:15 AM to 5:45 PM gives |
|---|---|---|
| Decimal hours | =(B2-A2)*24 | 8.5 |
| Total minutes | =(B2-A2)*1440 | 510 |
| Total seconds | =(B2-A2)*86400 | 30600 |
| Complete hours only | =INT((B2-A2)*24) | 8 |
You may also see =HOUR(B2-A2) and =MINUTE(B2-A2) suggested. They pull one piece out of the result (8 and 30 here), but HOUR returns only the hour of the clock, from 0 to 23. A 36-hour difference gives 12, not 36. Use the multiplication method when totals can pass a full day.
If you need the result as a label inside a sentence, =TEXT(B2-A2,"[h]:mm") should show total elapsed hours and minutes. The square brackets around h are meant to keep the count from resetting at 24, so check one result against the multiplication method before relying on it. The TEXT function returns text, though, so you cannot add or total those cells afterward.
Worked example: a small timesheet with breaks
Three fictional employees log a start time, an end time and an unpaid break. Jordan works overnight. Column B holds the start, C the end, D the break (typed as 0:30, 0:45 and 1:00), and E the hours worked.
| Name | Start (B) | End (C) | Break (D) | Worked (E) |
|---|---|---|---|---|
| Alex | 8:30 AM | 5:00 PM | 0:30:00 | 8:00:00 |
| Jordan | 10:00 PM | 6:15 AM | 0:45:00 | 7:30:00 |
| Priya | 7:45 AM | 4:15 PM | 1:00:00 | 7:30:00 |
| Total | 23:00:00 |
- In E2, type
=MOD(C2-B2,1)-D2and press Enter. The MOD part handles Jordan’s overnight row, and the break is subtracted after the shift length is known. - Drag the fill handle from E2 down to E4.
- In E5, type
=SUM(E2:E4). - Select D2:E5 and choose Format > Number > Duration. E5 shows 23:00:00.
- For pay, type
=E5*24in F5 and format it as Number. It returns 23.
Notice what happens if you skip step 4 and E5 stays in Time format: the total displays as 11:00:00 PM, because a clock face cannot show more than 24 hours. That single formatting choice is behind most “my hours are wrong” problems.
When the answer looks wrong
- A small decimal like 0.35. Not an error. The cell is in Number or Automatic format. Switch it to Duration, or multiply by 24 if you wanted hours.
- A clock time such as 8:30:00 AM instead of 8:30:00. The result cell is in Time format. Change it to Duration.
- A negative result. Either the cells are reversed (put the later time first) or the span crosses midnight. Use MOD or the add-a-day formula above.
- #VALUE! One of the cells holds text rather than a time. Retype it with colons, such as
10:00 PM. If the times came from an import,=TIMEVALUE(A2)converts a time written as text into a number between 0 and 1, and the TIMEVALUE reference notes that any date in the text is ignored. - Off by 12 hours. A cell that shows 5:00 may hold 5:00 AM when you meant 5:00 PM. Click it and read the formula bar, and type AM or PM every time.
- Subtracting a large fixed amount gives too little. You used
TIMEwith 24 or more hours. Switch to the divide-by-24 form.
Check and fix your result
A quick sanity check: temporarily set the result cell to Format > Number > Number and multiply by 24 in your head. 0.3541666667 times 24 is 8.5, which matches 8 hours 30 minutes. You can also add the result back to the start time; =A2+C2 should equal the end time.
If you change a format or overwrite a formula by mistake, press Ctrl+Z (Command+Z on a Mac) or use Edit > Undo. To clear a format and start over, select the cells and choose Format > Clear formatting.
Other things people ask about time math in Sheets
How do I subtract a time from right now?
Use =NOW()-A2 and format it as Duration. NOW recalculates as the sheet changes. For a fixed timestamp that will not change, press Ctrl+Shift+; on Windows or Command+Shift+; on a Mac to insert the current time into a cell, as listed in the Sheets keyboard shortcuts.
Can I get 0 instead of a negative duration?
Yes. Wrap the subtraction in MAX: =MAX(0,B2-A2) returns zero whenever the difference would be negative. That suits countdowns and remaining-time columns, but do not use it on overnight shifts, where the negative value is a sign you need MOD.
How do I subtract one duration from another, like 90:50 minus 55:00?
Type both into cells formatted as Duration, then use =A2-B2. The result is 35:50:00. Typed directly into a formula as text, TIMEVALUE is the wrong tool because it only returns values under one day; =VALUE("90:50:00")-VALUE("55:00:00") should convert the text to numbers first, since VALUE reads any time format Sheets understands. Format that result as Duration too.
Is overtime just another subtraction?
Yes. With total hours worked in E2, =MAX(0,E2-TIME(8,0,0)) gives the time over an 8-hour day in Duration format, and =MAX(0,E2*24-8) gives the same figure as decimal hours.
Do the same formulas work in Excel?
Mostly, since Excel also stores times as fractions of a day, but the formatting menus are different. See how time subtraction works in Excel for those steps.
Next step: set up a test row with a day shift and an overnight shift, apply =MOD(end-start,1), and format the results as Duration. Once both rows read correctly, copy the formula down the rest of your sheet.