Extracting Date and Time from a DateTime Value

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.

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.

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.

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.

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.

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