Read duration text like -2.01:30:15
Turn day.hh:mm:ss text, as .NET systems export it, into a signed number of days.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | IF(LEFT(A8,1)="-", … ) | Drops a leading minus. LET calls the rest t. | "2.01:30:15" |
| 2 | IFERROR(--TEXTBEFORE( … ),0) | Takes the days before the dot, or 0. LET calls it days. | 2 |
| 3 | IFERROR(TEXTAFTER( … ),t) | Takes the clock time after the dot. LET calls it clockText. | "01:30:15" |
| 4 | IF( … )*( … ) | Adds the days and clock time, then puts the minus back. | -2.062674 |
| 5 | LET( … ) | Names each step so the formula reads in order. | -2.062674 |
head is the text before the first colon (2.01). Excel counts time in days.
=LET(t,IF(LEFT(A8,1)="-",MID(A8,2,99),A8),head,TEXTBEFORE(t,":"),days,IFERROR(--TEXTBEFORE(head,"."),0),clockText,IFERROR(TEXTAFTER(t,"."),t),IF(LEFT(A8,1)="-",-1,1)*(days+TIMEVALUE(clockText)))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 durations. Paste durations like -2.01:30:15 or 01:30:15 into the green column of the Template tab. The days 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: LET function
OTHER ROUTES YOU MIGHT LIKE

Read a long file time number kept as text
134382762150000000 → November 4, 2026 2:30:15 PM
Get the template →
Read a long tick number kept as text
639293994150000000 → November 4, 2026 2:30:15 PM
Get the template →
Read duration text like P2DT1H30M15S
P2DT1H30M15S → 49:30:15
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →