Excel Display Time from Date Time

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 Custom Format

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

text2

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

https://support.office.com/en-us/article/Create-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4?CorrelationId=f9dfc98c-1186-44d5-b23c-5843840ad1ec&ui=en-US&rs=en-US&ad=US

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *