Read elapsed time text like 49:30:15
Turn the text 49:30:15 into a real duration that keeps counting past 24 hours.
Download Excel templateMicrosoft 365
What you'll make

The formula

| Number | Part | What it does | Result so far |
|---|---|---|---|
| 1 | TEXTBEFORE(A8,":")/24 | Takes the hours before the first colon, counted in days. | 49:00:00 |
| 2 | TEXTBEFORE(TEXTAFTER(…),":")/1440 | Takes the minutes between the colons and adds them. | 49:30:00 |
| 3 | TEXTAFTER(A8,":",-1)/86400 | Takes the seconds after the last colon and adds them. | 49:30:15 |
Excel counts time in days, so each piece is divided into days. Adding them never resets at 24 hours.
=TEXTBEFORE(A8,":")/24+TEXTBEFORE(TEXTAFTER(A8,":"),":")/1440+TEXTAFTER(A8,":",-1)/86400How 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 49:30:15 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
If you copy a duration elsewhere, give that cell the format [h]:mm:ss. Otherwise 49:30:15 shows as 1:30:15.
Microsoft's help: TEXTBEFORE function
OTHER ROUTES YOU MIGHT LIKE

Read minutes:seconds text like 2970:15
2970:15 (minutes:seconds) as text → 49:30:15
Get the template →
Read duration text like PT49H30M15S
PT49H30M15S → 49:30:15
Get the template →
Read days and clock text like 2 01:30:15
2 01:30:15 → a 49:30:15 duration
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →