Table of Contents
Converting a datetime to time in Excel refers to the process of extracting the time component from a date and time value. This can be achieved by using the TIME function or by formatting the cell to display only the time portion of the datetime value. This allows for easier data analysis and manipulation, particularly in scenarios where only time data is required. This feature is useful for various applications such as tracking work hours, scheduling events, and calculating time differences. With the proper use of Excel’s functions and formatting options, converting datetime to time can be easily accomplished.
Convert Datetime to Time in Excel
You can use one of the following formulas to convert a datetime to a time in Excel:
Formula 1: Convert Datetime to Time with AM/PM (e.g. 2:14 AM)
=TEXT(A2,"H:MM AM/PM")
Formula 2: Convert Datetime to Time without AM/PM (e.g. 2:14)
=TEXT(A2,"H:MM")
Formula 3: Convert Datetime to Time with Hours, Minutes and Seconds (e.g. 2:14:00)
=TEXT(A2,"H:MM:SS")
Each of these formulas assumes the datetime is located in cell A2.
The following example shows how to use each of these formulas in practice.
Example: Convert Datetime to Time in Excel
Suppose we have the following column of datetimes in Excel:
Suppose we would like to convert each datetime in column A to only a time.
We can type the following formulas into cells B2, C2 and D2 to convert each datetime in cell A2 to a time with a specific format:
- B2: =TEXT(A2,”H:MM AM/PM”)
- C2: =TEXT(A2,”H:MM”)
- D2: =TEXT(A2,”H:MM:SS”)
We can then click and drag these formulas down to each remaining cell in each column:
How This Formula Works
Recall the formula that we typed into cell B2 to convert the datetime in cell A2 to a date:
=TEXT(A2,"H:MM AM/PM")
This formula uses the TEXT function, which allows you to change the way that a number appears by using a specific format code.
In this formula, we used the format code of H:MM AM/PM which tells Excel to display the hours (H), minutes (M) and AM or PM (AM/PM) with a colon in between the hours and minutes.
The end result is that we’re able to display only the portion of the datetime that contains the time.
The date portion of the datetime is simply not shown at all.
Note: You can find the complete documentation for the TEXT function in Excel .
Additional Resources
The following tutorials explain how to perform other common operations in Excel: