This Excel tutorial explains how to extract time from Date Time (timestamps) or extract date from Date Time in Excel and how to convert date time to time serial (decimal number).
You may also want to read:
Excel Access VBA extract percentage from text
Access Excel VBA extract number from text or extract alphabet
Access Excel extract file name from file path
Excel Display Time from Date Time
Excel Extract Time from Date Time / extract date from Date Time
Sometimes you have date time value in Excel worksheet, like 3/1/2015 10:43 AM, but you only want to take out the time (10:43 AM) or date (3/1/2015) for manipulation, such as filter base on time regardless of date. There are a few ways to do that as long as you understand how Excel treats Date Time format. In the below sections, I will explain the mechanism of Date Time and then I will show you how to extract.
Excel Date Mechanism
Each date you enter can be represented by a numeric value. For example, type 1/1/2014 in a Cell and then go to Format Cells, change Category to “General”.
1/1/2014 becomes 41640, now try 1/1/1900 (Jan 1)
1/1/1900 becomes 1
2/1/1900 becomes 2
Now you see Excel views 1/1/1900 as the first day, and add one day up until 1/1/2014, a total of 41640 days.
You can verify it by using DATEDIF Function to find the date difference between these two days.
=DATEDIF("1/1/1900","1/1/2014","D")
The formula returns 41639, which is 1 day less, because DATEDIF function counts 2/1/1900 as the first date difference, but you see my point.
Excel Time Mechanism
If you don’t specify a date when you enter a time in a Cell, you will see the default date is 0/1/2014 (Jan 0) if you change the Format to Date Time or Date.
To illustrate Excel Time, take 8:00 AM as an example. Type 8:00:00 in a Cell, then go to Format Cells, change Category to “General”.
Now the Cell display 0.33333, so why is that 0.33333 represents 8:00:00?
It is not difficult to understand, you see Excel converts date to an integer, then numeric value of time must be in form of day as well.
Therefore 0.33333 means 8 hours (1/3 day). Let take some more examples below,
For 0:00:00, day value is 0
For 24:00:00, day value i s 1
Extract Time from Date Time
Now that you have already understood the mechanism of Date Time, here comes the extraction part to extract time from date time, below are two ways to do this.
Method 1
Use INT Function to extract the integer part of the date time (which is the date), and then subtract it from the original value (date+time) in order to get the decimal part (time).
For example, you have 3/1/2015 10:43 AM (42007.4465) in cell A1. Type the following formula in any cell.
=A1 - INT(A1)
= 42007.4465 – 42007
= 0.4465 (or 10:43 AM)
Method 2
Use MOD function to get the remainder after dividing the date time by 1. For example, 5.123 divided by 1, the remainder is 0.123. This is a workaround to extract the decimal part.
=MOD(A1,1)
=MOD(42007.4465,1)
= 0.4465 (or 10:43 AM)
Extract Date from Date Time
Use INT Function to extract the integer part of the date time (which is the date)
For example, you have 3/1/2015 10:43 AM (42007.4465) in cell A1. Type the following formula in any cell.
=INT(A1)
=3/1/2015 0:00