Excel Convert Hour Minute Second Date: How-To

To convert a time stored as text like “1h 30m 15s” into a real Excel time value (a decimal fraction of a day), use a formula that parses the hours, minutes, and seconds and divides by 24, 1440, and 86400 respectively. For a full date and time string such as “2025-01-15 14:30:00”, you can use the DATEVALUE and TIMEVALUE functions together. This article explains the exact formulas, how to handle common text formats, and how to avoid the pitfalls that trip up most users.

Formula

The core conversion relies on Excel’s internal time system: one day equals 1, so an hour is 1/24, a minute is 1/(24*60) = 1/1440, and a second is 1/(24*60*60) = 1/86400.

• If you have separate cells for hours (H), minutes (M), and seconds (S), the time value is =H/24 + M/1440 + S/86400.
• If you have a single text string like “1:30:15” (h:mm:ss), use =TIMEVALUE("1:30:15") – it returns a decimal time value.
• For a full date and time text like “2025-01-15 14:30:00”, use =DATEVALUE("2025-01-15") + TIMEVALUE("14:30:00") – the integer part is the date, the decimal part is the time.

All results are in Excel’s serial date/time format. To display them as a clock time, apply a time number format (e.g., h:mm:ss). To display as a duration, use [h]:mm:ss which allows hours greater than 24.

Worked Example

Using the calculator above, enter 90 minutes. The formula converts minutes to days: 90 / 1440 = 0.0625 days. That equals 1.5 hours, or 1:30 in h:mm format. If you type =90/1440 in a cell and format it as h:mm, you’ll see 1:30.

Why Excel Stores Time as Fractions

Excel stores dates and times as serial numbers where 1 = January 1, 1900, and the fractional part represents the time of day. For example, 0.5 is noon, 0.25 is 6:00 AM. This design allows arithmetic: you can add 0.25 to a date to get 6 hours later. When you convert text to a time, you’re really just converting to that decimal fraction.

How to Convert Text to Time in Excel

1. Use TIMEVALUE for standard time text – If the text looks like “14:30:00”, =TIMEVALUE(A1) works.
2. Use DATEVALUE for date text – For “2025-01-15”, =DATEVALUE(A1) returns the serial date.
3. Combine for full datetime – =DATEVALUE(A1)+TIMEVALUE(B1) if date and time are in separate cells.
4. For custom text like “1h 30m” – Use a formula that extracts numbers: =LEFT(A1,FIND("h",A1)-1)/24 + MID(A1,FIND("h",A1)+1,FIND("m",A1)-FIND("h",A1)-1)/1440.
5. For a single text with h, m, s – Use a more complex extraction with SEARCH and MID, or use the SUBSTITUTE trick: =TIMEVALUE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A1,"h",":"),"m",":"),"s","")) – but this only works if the text is exactly like “1h30m15s”.

Common Mistakes and Misconceptions

• Not formatting the result as a time – A decimal like 0.0625 looks like a number, not a time. Always apply a time format.
• Using 24 instead of 1440 – Many people try =A1/24 for minutes, but that gives hours, not minutes. Minutes require dividing by 1440.
• Confusing date and time serial numbers – A full datetime is the sum of the date integer and time decimal, not a single division.
• Text with leading/trailing spaces – Use TRIM() to clean before conversion.
• Locale issues – Excel’s DATEVALUE and TIMEVALUE depend on your system’s date/time settings. A text like “01/15/2025” may be interpreted as January 15 or 1 January depending on locale.

How to Choose the Right Method

If your text is in a standard format (like ISO 8601 or a typical locale format), use DATEVALUE and TIMEVALUE directly. If it’s a custom format with letters like “h”, “m”, “s”, you’ll need to extract numbers manually. If you have separate numeric cells for hours, minutes, seconds, just do the division arithmetic. If you need to convert a duration (like 90 minutes) to a time-of-day, remember that durations are not times of day – a duration of 90 minutes is 1:30, but a time of day 1:30 is 1:30 AM. The same serial number can represent both, but the interpretation differs.

Alternatives to Formulas

• Text to Columns – For text like “1:30:15”, you can use Data > Text to Columns with the colon as delimiter, but that splits into separate columns, not a time value.
• Power Query – You can split columns and parse times using the UI, then change the data type to time.
• VBA – A small macro can parse complex strings, but it’s overkill for most cases.
• Using the TIME function – If you have separate hour, minute, second cells, =TIME(H,M,S) is the cleanest.

Frequently Asked Questions

How do I convert “1h 30m 15s” to a time value?

Use a formula that extracts each number. For example, if A1 contains “1h 30m 15s”, use =LEFT(A1,FIND("h",A1)-1)/24 + MID(A1,FIND("h",A1)+2,FIND("m",A1)-FIND("h",A1)-2)/1440 + MID(A1,FIND("m",A1)+2,FIND("s",A1)-FIND("m",A1)-2)/86400. This assumes the format is exactly as shown.

Why does my TIMEVALUE return #VALUE!?

That error means the text isn’t recognized as a valid time. Check for extra spaces, non-breaking spaces, or a different locale format. Use =TRIM(A1) first, and ensure the text uses a colon (:) not a period (.) as the separator.

Can I convert a date and time in one cell like “2025-01-15 14:30”?

Yes, use =DATEVALUE(LEFT(A1,10)) + TIMEVALUE(RIGHT(A1,5)) if the text is exactly 16 characters. Or use =VALUE(A1) if Excel already recognizes it as a date/time – but that may not work for all text formats.

How do I convert a time to minutes or seconds?

If you have a time value in A1, multiply by 1440 to get minutes, or by 86400 to get seconds. For example, =A1*1440 gives total minutes.

What is the difference between a time and a duration in Excel?

A time of day is a fraction of a 24-hour day (e.g., 0.5 = 12:00 PM). A duration can exceed 24 hours; to display it correctly, use the format [h]:mm:ss which shows total hours, not just the hour of the day.

How do I convert a text like “3:45 PM” to a time value?

Use =TIMEVALUE("3:45 PM") – it works with AM/PM. Make sure the text includes the space before AM/PM. If it’s in a cell, just reference the cell.

Related

• Force Reader Mode Every Webpage? How To
• Vtuber Agency vs Independent: Which Is Better?
• Screen Mirror iPad to Firestick: Full Guide

in t | Total minutes | min | 90 | 0
out d | Days | days | t / 1440 | 4
out h | Hours | h | t / 60 | 2
out hm | h:mm | h:mm | t / 1440 | time