Read elapsed time text like 49h 30m
Turn the text 49h 30m 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,"h")/24 | Takes the hours before the h, counted in days. | 49:00 |
| 2 | TEXTAFTER(A8,"h ") | Takes what comes after the h and space. | "30m" |
| 3 | TEXTBEFORE( … "m") | Keeps what comes before the m: the minutes. | "30" |
| 4 | /1440 | Divides by 1,440 minutes in a day and adds them. | 49:30 |
Excel counts time in days. Parts 2 and 3 work from the inside out.
=TEXTBEFORE(A8,"h")/24+TEXTBEFORE(TEXTAFTER(A8,"h "),"m")/1440How 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 49h 30m 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. Otherwise 49:30 shows as 1:30.
Microsoft's help: TEXTBEFORE function
OTHER ROUTES YOU MIGHT LIKE

Read elapsed hours text like 49,5
49,5 hours → 49:30 elapsed
Get the template →
Read elapsed time text like 49:30
49:30 as text → 49:30, not 1:30
Get the template →
Read elapsed time text like 49:30:15
49:30:15 as text → 49:30:15, not 1:30:15
Get the template →
Show an appointment time as 1:05 PM
13:05:00 → 1:05 PM
Take this route →