ExcelHow

How to Convert Time Format from 12-Hour Clock to 24 OR 24 to 12-Hour Clock in Excel

,

Sometimes we need to enter time into excel, and as we all know there are several formats to show the time. We often use 12-hour clock or 24-hour clock to show the time like 3:01:01PM or 15:01:01, one time displays in two different formats. So if we want to convert time format between them, how we can do?

This article will show you the method to convert 12-hour clock to 24-hour clock in excel and vice versa using formula and User Defined Function with VBA code.

1. Preset Time Format in Excel

If you just want to enter Time into excel by 12-hour clock or 24-hour clock format, you can preset your cell format to a proper one before entering the time.

Step1: Open a blank excel, select column A for example, right click, select ‘Format Cells…’.

24-Hour Clock 1

Step2: In Format Cells, under Number tab, select Time in Category list. Then all Types are loaded in the right. See below screenshot:

24-Hour Clock 2

Step3: You can select the Type you expect to show the time when you entering time data into excel. For example, we select the first one 12-hour format to show the time, click OK on Format Cells to save the preset. Then in A1, we enter 13:55:55 directly. After inputting and clicking Enter, 1:55:55 PM is auto displayed in the cell.

24-Hour Clock 3

If you already have a list of time in existing excel worksheet, how can we convert time format to expect time format? You can follow below steps to convert time format.

2. Convert from 12-Hour Clock to 24-Hour Clock Using Formula

Step1: Prepare a list of time in excel. See below.

24-Hour Clock 4

Step2: In column B->B1, enter the following formula. click Enter. Verify that PM is cleared properly.

=TEXT(A1,"HH:MM:SS")

Step3: Copy B1 to B2 and B3 (or hold B1 and drag down to B3 directly). Time in A column is converted to 24-hour clock properly now.

24-Hour Clock 5

3. Convert from 24-Hour Clock to 12-Hour Clock using Formula

Step1: Following above steps, a list of 24-hour format time is displayed in B column. Now in column C->C1, enter the formula:

=TEXT(A1,"HH:MM:SS AM/PM")

Verify that 24-hour format is converted back to 12-hour format.

24-Hour Clock 6

Step2: Use the same way to fill C2 and C3.

24-Hour Clock 7

Step3: Be aware that the slash is “/”, if you entered “\” you may get below result improperly.

24-Hour Clock 8

4. Convert from 12-Hour Clock to 24-Hour Clock using User Defined Function

You can use a User-defined function with VBA code to convert time format from 12-hour clock format to 24-hour clock format in Excel. Just do the following steps:

Step1: Press Alt + F11 to open the VBA Editor.

Adding Comma Character at End of Cells vba1.png

Step2: In the VBA Editor, click Insert > Module.

Adding Comma Character at End of Cells vba1.png

Step3: Copy and paste the below code into the module. Save the module and close the VBA Editor.

Convert Time Format from 12-Hour Clock to 24 clock format VBA 1.png
Function Convert12to24TimeFormat_ExcelHow(timeValue As String) As String
    Dim convertedTime As String
    convertedTime = Format(timeValue, "hh:mm:ss AM/PM")
    Convert12to24TimeFormat_ExcelHow = Format(timeValue, "HH:mm:ss")
End Function

Step4: enter the following formula in a blank cell:

=Convert12to24TimeFormat_ExcelHow(A1)

Where A1 is the cell containing the time in 12-hour clock format that you want to convert.

Step5: Press Enter to apply the formula to the cell.

Convert Time Format from 12-Hour Clock to 24 clock format VBA 2.png

The function first converts the time value to the 12-hour clock format using the Format function with the “hh:mm:ss AM/PM” format code. It then converts the 12-hour clock format to the 24-hour clock format using the Format function with the “HH:mm:ss” format code. Finally, it returns the converted time value as a string.

Note: This function assumes that the time value is in a standard Excel time format (e.g., “12:00 PM”, “1:30 AM”, etc.). If your time values are in a different format, you may need to adjust the code to match the format of your time values.

5. Convert from 24-Hour Clock to 12-Hour Clock using User Defined Function

You can also use an User Defined function in Excel with VBA code that convert a time in 24-hour clock format to 12-clock clock format.  Here is the sample code:

Convert Time Format from 24-Hour Clock to 12 clock format VBA 1.png
Function Convert24to12TimeFormat_ExcelHow(timeValue As Variant) As String
         Dim convertedTime As String
        convertedTime = Format(timeValue, "hh:mm:ss")
        If convertedTime < "12:00:00" Then
            convertedTime = convertedTime & " AM"
        Else
            convertedTime = Format(timeValue - TimeSerial(12, 0, 0), "hh:mm:ss") & " PM"
        End If
        Convert24to12TimeFormat_ExcelHow = convertedTime
End Function

In a blank cell, type the following function, and press enter to apply the formula to the cell.

=Convert24to12TimeFormat_ExcelHow(D1)
Convert Time Format from 24-Hour Clock to 12 clock format VBA2.png

Note: This function assumes that the time value is in a standard Excel time format (e.g., “12:00:00”, “13:30:00”, etc.). If your time values are in a different format, you may need to adjust the code to match the format of your time values.

6. Video: Convert Time Format from 12-Hour Clock to 24 OR 24 to 12-Hour Clock in Excel

This video will demonstrate how to convert time format from 12-hour clock to 24-hour clock or vice versa in Excel using both Text formulas and VBA code.

Recent Posts