Extracting Date and Time from a DateTime Value

Philip Treacy

September 24, 2019

Dates and times are stored in Excel as decimals known as serial numbers. The date is represented by the whole number part of the serial number and the time by the decimal part.

In Excel dates start from 1 Jan 1900, which is represented by the number 1.

When you see a date or a time displayed in Excel like 19 June 2020 this is because formatting has been applied to the underlying serial number to display it to you in a way that makes sense.

If you wanted to, you could just type in 44001 and then apply a date format to that cell and see that it is June 19th 2020.

Times start from midnight, 0 or 0.00 represents midnight.

There are 24*60*60 seconds in a day, a total of 86400. So 1/86400 represents 1 second, which is equal to 0.0000115740740740741.

Midday is 0.5, 6.00pm is 0.75

Understanding that dates and times are really decimal numbers makes it straight forward to extract a date or a time.

Other Articles on Date and Time in Excel

If you need a refresher on date and time then check out these posts.

Excel Date and Time

Calculating Time in Excel

Excel Time Calculation Tricks

Excel Date and Time Formatting

Extracting a Date

In order to extract the date we need to get rid of the decimal part of the serial number. There are two functions we can use to do this.

The first one is TRUNC which truncates a decimal by removing the fractional part leaving just an integer.

Using TRUNC on our datetime removes the time portion of the serial number, leaving us with just the date.

TRUNC function to extract date

The other function we can use is INT which does effectively the same thing. However Whilst TRUNC removes the fractional part of the number, INT rounds down.

As all time values are less than 1, 11:59:59 PM = 0.999988425925926, rounding down will always result in a fractional part equal to 0.

INT function to extract date

Extracting a Time

To extract the time part of a datetime serial number we need to get rid of the date part, or the whole number part of the serial.

We can do this a couple of different ways, one using the MOD function, and the other using TRUNC.

MOD returns the remainder after you divide one number by another. For example, MOD(5,2) gives the result 1 because you can divide 2 into 5 twice and have a remainder of 1.

Put another way, 5 = 2 x 2 + 1.

If we use a divisor of 1 on an integer e.g. MOD(5,1) the result is 0 because there is no remainder. 5 is divisible by 1, exactly 5 times.

If we use the same idea on a decimal e.g. MOD(5.23,1), 5 is still divisible by 1 exactly 5 times but we now have a remainder of 0.23.

If we have a datetime of 19 Jun 2020 8:23:00 AM, this serial number is 44001.35. MOD(44001.35,1) leaves us with a remainder of 0.35, which is the time.

MOD function to extract time

For the maths fans out there, you can express MOD in terms of the INT function :

MOD(num, div) = num - div*INT(num/div)

where num is your number (datetime serial number) and div is your divisor (1).

To get the time using TRUNC we truncate the datetime, leaving just the date part, then subtract that from the datetime, leaving us with the time.

TRUNC function to extract time

Combining Date and Time

If you have separate date and time values and need to combine them, just add them together.

Download eBook and Sample Workbook

Enter your email address below to download the workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

6 thoughts on “Extracting Date and Time from a DateTime Value”

  1. Thanks for this clear explanation. once you know that dates are nothing but a serie of numbers starting at 1 (1/1/1900) a lot of things almost explain themselves.
    But.
    The biggest problem (in myopinion) with dates is that in the communication between VBA and excel (cell values) things get confused. Especially for those of us that work with different notations of a date then the US way.

    Reply
    • Indeed,
      You have to know how to handle dates in VBA. I have to mention if one needs to get dates from a closed file, and that file was saved from a different time system computer, there is no way to get the original dates, excel will convert dates when the file is open, the code will read the values AFTER excel automatic conversion. A different approach is needed, to connect to the file via ODBC without opening the file. Or, get data with power query.

      Reply
  2. Just a minute Philip… I was watching the great Excel for Operations Management training at MOTH and liked =F8-TRUNC(F8) as a method of parsing the time from a date/time value (where F8 contains the date/time value)!

    is there any difference?

    Thanks!

    Reply
  3. I always learn something by reading your posts. Using MOD to extract the time from a date/time was the key takeaway this week. Brilliant!

    Reply

Leave a Comment

Current ye@r *