time from DateTime in Excel

Extract time from DateTime in Excel

The fastest way to extract time in Excel is by using the function MOD. The function MOD in Excel returns the remainder after a number divided by a divisor. In other words, you can extract decimals. The time part in DateTime is decimal numbers; by using MOD, you can get only the fractional part. By using this trick, you can get only time from DateTime in Excel.


Extract time from DateTime in Excel

Here is my date and time combination. At the same time, if it is formatted as a number, you can see that time is represented as decimals.

Excel datetime formatted as numerical
22 hours and 11 minutes is about 92% of the whole day.

With function MOD you can divide the date and time by one (nothing changes) and return a reminder (time).


time from datetime in Excel, MOD in Excel

There might be necessary to adjust the format if the result contains the date and time like this.

00.01.1900  22:11:00

If you want to get a specific time format in Excel, try to use format code. Here are great examples of what you can do with that to format Excel chart axis values conditionally or as thousands.

Alternatively, you can get time from DateTime in Excel by using the function TRUNC that removes the fractional part of the number, like in this example. Mathematically you can get the same result as previously.


time from datetime in Excel, TRUNC in Excel


Date from date and time in Excel

If the Excel function TRUNC removes decimals, you can also use that to get the date part from date and time.


date from datetime in Excel




Leave a Reply

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