Read negative elapsed time like -0:30
Turn the text -0:30 into minus 30 minutes, keeping the sign even with zero hours.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | IF(LEFT(A8,1)="-",-1,1) | Gives -1 when the text starts with a minus. | -1 |
| 2 | ABS(TEXTBEFORE(A8,":")) | Takes the hours before the colon, without the minus. | 49 |
| 3 | /24 | Divides the hours by 24 to count them in days. | 2.0417 (49:00) |
| 4 | TEXTAFTER(A8,":")/1440 | Takes the minutes and adds them, counted in days. | 2.0625 (49:30) |
Excel counts time in days. The * puts the sign on the whole duration, so -49:30 gives -2.0625 and -0:30 stays negative.
=IF(LEFT(A8,1)="-",-1,1)*(ABS(TEXTBEFORE(A8,":"))/24+TEXTAFTER(A8,":")/1440)How to use it
Yellow bar at the top? Click Enable Editing.
- See it work. The Example tab shows 20 made-up examples with the formula already in.
- Use your own times. Paste elapsed times like -0:30 or 49:30 into the green column of the Template tab. The durations fill in by themselves.
- Move them into your letter. Copy the results. Then use Paste Special and choose Values.
Before you send
Keep these results as plain numbers. Excel shows #### for a negative time in a time format.
Microsoft's help: TEXTBEFORE function
OTHER ROUTES YOU MIGHT LIKE

Convert noon or midnight to clock times
Noon → 12:00 PM; midnight → 12:00 AM
Get the template →
Read 24:00 as midnight the next day
November 4, 2026 + 24:00 → November 5, 2026 12:00 AM
Get the template →
Write a duration as days, hours, minutes
49:30 → 2d 1h 30m
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →