Excel Formulas to Convert 12-Hour Time to 24-Hour Format

Manually converting 12-hour AM/PM timestamps in Excel often leads to tedious formatting errors and disrupted reporting workflows. This analytical friction frequently arises when consolidating time-tracking data derived from standard funding sources, where inconsistent inputs stall progress. Resolving this discrepancy grants users immediate analytical clarity and flawless data alignment across systems.

As a stipulation, Excel requires target cells to be formatted specifically as 24-hour time (hh:mm) rather than text to maintain formula integrity. For example, converting "6:30 PM" to "18:30" is easily achieved using the TIMEVALUE function. Below, we provide the precise formulas to automate this conversion.

Understanding How Excel Handles Time

Before diving into formulas, it is crucial to understand how Microsoft Excel manages and stores time. To Excel, time is not represented as "12:00 PM" or "24:00" internally. Instead, Excel treats dates and times as serial numbers. Specifically, time is stored as a decimal fraction of a 24-hour day.

For example:

• 0.0 represents 12:00:00 AM (midnight)
• 0.25 represents 6:00:00 AM (one-quarter of a day)
• 0.5 represents 12:00:00 PM (noon)
• 0.75 represents 6:00:00 PM (three-quarters of a day)

Because Excel stores time as a fractional decimal, converting between 12-hour and 24-hour formats is often just a matter of changing how the data is displayed (formatting) rather than changing the underlying values. However, if your time is imported as text, you will need logical formulas to convert it. This guide covers both scenarios comprehensively.

Method 1: Change Formatting (The Non-Formula Approach)

If your times are already stored as proper Excel time values (decimals), you do not need a formula. You can toggle between 12-hour and 24-hour formats instantly using Custom Number Formatting.

Steps to Convert 12-Hour to 24-Hour Format:

1. Select the cells containing the times you want to convert.
2. Right-click and select Format Cells... (or press Ctrl + 1).
3. In the Category list on the left, select Custom.
4. In the Type input field, enter one of the following codes:
   • hh:mm (Displays hours with a leading zero, e.g., 08:30 or 14:45)
   • h:mm (Displays hours without a leading zero, e.g., 8:30 or 14:45)
   • hh:mm:ss (Includes seconds, e.g., 14:45:30)
5. Click OK.

Steps to Convert 24-Hour to 12-Hour Format:

To go in the opposite direction, follow the same steps but use these formatting codes in the Custom field:

• hh:mm AM/PM (Displays hours with leading zero, e.g., 02:45 PM)
• h:mm AM/PM (Displays hours without leading zero, e.g., 2:45 PM)

Method 2: Convert Time to 24-Hour Text Using the TEXT Function

There are times when you need the output to be a text string rather than a formatted number. For instance, if you are concatenating the time with other text (e.g., "The shift starts at 14:30"), standard number formatting will fail, displaying the decimal serial number instead.

To convert a 12-hour time to a 24-hour text format, use the TEXT function:

=TEXT(A2, "hh:mm")

Conversely, if you want to force a time value into a 12-hour text string with AM/PM:

=TEXT(A2, "hh:mm AM/PM")

Method 3: Converting Text-Based Times to Real Times

A common issue when importing data from external software or databases (like SQL or Salesforce) is that times are imported as static text strings (e.g., "02:30 PM" or "14:30" stored as text). Excel cannot perform mathematical calculations on text strings.

Using TIMEVALUE to Parse Text

If you have a 12-hour text string like "2:30 PM" and want to convert it into a real Excel time value (which can then be formatted to 24-hour time), use the TIMEVALUE function:

=TIMEVALUE(A2)

Once you apply this formula, Excel will return a decimal value (e.g., 0.60416). Simply apply the Custom Number Formatting hh:mm to this cell as described in Method 1 to display it as 24-hour military time.

Handling Irregular Text Formats (e.g., "1430" or "230PM")

Sometimes, raw data does not include colons or spaces. Here is how to handle these common anomalies:

Case A: Military Time written without a colon (e.g., "1430")

If you have a four-digit integer or text representing military time, use this combination of REPLACE and TIMEVALUE:

=TIMEVALUE(REPLACE(A2, LEN(A2)-1, 0, ":"))

How it works: The REPLACE function inserts a colon before the last two digits of the text string. TIMEVALUE then parses that structured string into a valid Excel time serial number.

Case B: Raw integers (e.g., 1430 as a number value)

If your sheet contains military times stored as simple integers (like 1430 or 915), you can use mathematical division to extract the hours and minutes using the TIME function:

=TIME(INT(A2/100), MOD(A2, 100), 0)

How it works:

• INT(A2/100) divides 1430 by 100 and rounds down to get the hours (14).
• MOD(A2, 100) returns the remainder of the division, isolating the minutes (30).
• The TIME(hour, minute, second) function takes these arguments and generates a standard Excel time value.

Method 4: Converting 12-Hour Text without Spaces (e.g., "02:30PM")

If your text values are missing a space before the AM/PM designator (e.g., "02:30PM" instead of "02:30 PM"), the TIMEVALUE function might return a #VALUE! error depending on your system's regional settings.

To safely fix and convert this, use a formula that inserts a space before the "AM" or "PM" suffix:

=TIMEVALUE(REPLACE(A2, LEN(A2)-1, 0, " "))

This formula finds the position right before "AM" or "PM" (which are always 2 characters long at the end of the text string) and injects a space, allowing Excel to recognize and parse the time successfully.

Troubleshooting & Frequently Asked Questions

Why is my time formula returning a decimal number like 0.6125?

This is expected behavior! As discussed, Excel stores times as decimals. You simply need to change the format of the cell. Select the cell, press Ctrl + 1, and choose either a 12-hour or 24-hour time format under the Custom category.

How do I handle times that exceed 24 hours?

If you are summing up times (e.g., total project hours) and the sum goes beyond 24 hours, standard 24-hour formatting (hh:mm) will reset to zero after reaching 24. To display cumulative hours correctly, use the following square bracket custom format:

[h]:mm:ss

This tells Excel to preserve cumulative hours rather than cycling back to a new day format.

How to convert 24-hour time back to 12-hour time using text formulas?

If you have a 24-hour time value and need it explicitly outputted as text with AM/PM format, use:

=TEXT(A2, "hh:mm AM/PM")

Summary of Quick Formula Solutions

Here is a quick cheat sheet for converting your time configurations: