Go Back

Extract date from a date and time

Extract date from a date and time

In this article, I will explain to you How to extract a date from a date and time in Excel with Examples. Firstly you should know that Excel manipulates data in the form of dates and time using a simple method where dates are represented as serial numbers and times are represented as fractional values.

Figure 1. How to Extract date from a date and time in Excel

For instance, the date September 26, 2016, 21:30 is saved in Excel as the number 42639.89583. In this example, the number 42639.89583 is representing the date September 26, 2016, and .89583 is representing the time 21:30

Extract date from a date and time using INT(A1)

Using int (A1) where A1 is the cell containing the time and date and INT is the function. The time part of the date and time (which is saved as the part after the decimal) is discarded. Using the TRUNC function will also yield the same result.

Extract date from a date and time using TRUNC(A1)

The same thing will happen as above. However, there is a difference when it comes to signed integers.

       

Figure 2. How to Extract date from a date and time in Excel using INT and TRUNC command

Resultantly, using either one of the above methods will extract a date from a date and time in Excel. However, Make sure that you use a format for the date in the result that does not include time parameters. If not so you will see the time displayed just as before. That is not what we want in this case.

In Excel, for extracting dates in dates and times there is not much difference in using INT or TRUNC function for extracting an integer from a date and time. Usually, with INT function, it rounds the number down to the nearest index which makes a difference only when values are negative. Their functionalities are somewhat different but it doesn’t affect a person using positive integers like in this case.

Notes

  • Date and time can never be negative.

 

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Here are some problems that our users have asked and received explanations on

I need a formula that will split this date and time format form one cell to extract date only into another eg: 2018-06-14T10:43:21.183
Solved by G. W. in 25 mins
how do I extract the date from a cell?
Solved by G. H. in 27 mins
I am using an EXIF file extractor to extract date and time from photos. The time stamp on a certain set of photos is off my several days and several hours.How do I adjust the date and time, by a consistent amount, over a group of cells in excel? The end product would show that all date and times of the incorrectly time stamped photos, would now all be corrected by the same amount, and therefore show the correct date and time in excel.
Solved by X. S. in 29 mins

Leave a Comment

avatar