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.
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
Enter your email address below to download the workbook.
Examples of all functions used in this post are in the sample workbook.
You can also grab a copy of our eBook Excel Date and Time : Everything You Need to Know About Working With Date and Time in Excel
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.
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.
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.
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?
I missed that one! I’ll add it in.
It will work too, just another way to get the time.
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!
Thanks Patrick, glad this was useful to you 🙂