This Excel tutorial explains how to use Custom Format to display Time from Date Time.
You may also want to read:
Excel Extract Time from Date Time
Excel Display Time from Date Time
In my previous post, I have explained how to extract time from date time. What I mean “extract” is that the date is using formula to completely remove date from date time. In this tutorial, I will explain how to use custom format to display Date Time in Time format.
Custom Format to display Time
To change how Date Time is displayed, right click on the Cell containing the Date Time > Format Cells > Custom
Below are some Custom Formats for time.
| hh | Express hour from 01 to 24. If hour exceeds 24, a day will add and hour will be subtracted by 24 |
| mm | Express month in number, from 01 to 12 |
| ss | Express second from 01 to 59. If second exceeds 60, a minute will add and second will be subtracted by 60 |
| [hh] | Convert date and time to hours, allow you to display hour larager than 24 |
| [mm] | Convert date and time to minute, allow you to display minute larger than 60 |
| [ss] | Convert date and time to second allow you to display second larger than 60 |
Display in hh:mm format and ignore Date value
If you want to completely ignore the Date from Date Time, format the Cell as hh:mm
For example, 1/1/2014 1:01:01 is displayed as 01:01
Display in hh:mm format and accumulate Date value as hour
– If you want to display hh:mm but you want to convert Date (24 hour for 1 day) to hh, use [hh]:mm
For example, 1/1/2014 1:01:01 is displayed as 999361:01
99361 is the number of hours past since 1/1/1900 00:00:00
Similarly, you can use [mm] to convert Date+Hour to minute, use [ss] to add Date+Hour+Minute to second.
Convert to hh:mm Text format
If you want to use formula to do conversion instead of simply changing the display, you can use the Text formula. For example,
=TEXT(A1,”[hh]:mm”)
Convert to hh:mm Value format
=A1 – INT(A1)
or
=MOD(A1,1)
Outbound References
