Dates are cleverly stored as serial numbers while times are stored as decimal values relative to 24 hours in one day. While working with Excel, we can extract only the date portion by using the INT or TRUNC function. This step by step tutorial will assist all levels of Excel users in extracting date from a date and time using two methods.
Figure 1. Final result: Extract date from date and time
Formula 1: =INT(B3)
Formula 2: =TRUNC(B3)
Syntax of INT Function
INT rounds a number down to the nearest integer
=INT(number)
- number – the number we want to round down to the nearest integer
Syntax of TRUNC Function
TRUNC returns an integer by removing the fractional part of the number
=TRUNC(number, [num_digits])
- number – the number we want to truncate
- num_digits – Optional; a number that represents the digits after the decimal point specifying the precision of the truncation; If omitted, the default value for num_digits is 0 (zero).
TRUNC and INT are similar such that both functions return integers. TRUNC removes the fraction or decimal part of the number while INT rounds down the numbers to the nearest integer.
Setting up our Data
Our table consists of three columns: Date and Time (column B), Date & Time Value (column C) and Date (column D).
Figure 2. Sample date to extract date from date and time
The values in column B and C are the same, and column C is not needed in our formula. However, we only want to show here how Excel stores date and time through numbers. Let us display the value of the date and time by following these steps:
Step 1. Select cells C3:C9
Figure 3. Selecting C3:C9 to change format to numbers
Step 2. Press Ctrl + 1 to launch the Format Cells dialog box
Figure 4. Format Cells preview
Step 3. Change the format to Number, with 5 decimal places.
Figure 5. Changing the format to Number with 5 decimal places
Step 4. Click OK.
Figure 6. Column C showing values of date and time
We now have a table showing the values in column C. Note that the whole numbers represent the serial number for the date, while the decimal values represent the time.
Dates have serial numbers where the value 1 corresponds to the starting date January 1, 1900, and each succeeding day is incremented by one. One whole day has the value of 1 and one hour is equivalent to 1/24, since there are 24 hours in one day.
We want to extract the date from the date and time. This can be done by extracting only the whole numbers or integers, through the INT or TRUNC function.
Extract date from a date and time using INT
In order to extract the integers that correspond to the date, we will be using the INT function through these steps:
Step 1. Select cell D3
Step 2. Enter the formula: =INT(B3)
Step 3. Press ENTER
The INT function rounds down any number into the nearest integers. We have now extracted only the whole number or integers by using INT function.
Figure 7. Extracting serial number only from date and time using INT
Step 4. Format the cell to show the date by pressing Ctrl + 1
Step 5. Select the category Date and the Type: d/mm/yyyy
Figure 8. Changing format to show date
Figure 9. Display changed from serial number to date
Step 6. Copy the formula in D3 to cells D4:D9 by clicking the “+” icon at the bottom-right corner of cell D3 and dragging it down
Figure 10. Output: Extract date from date and time using INT
Extract date from a date and time using TRUNC
An alternative method to extract the date from a date and time is by using the TRUNC function. The procedure is the same as in the previous method, but we only replace the function with TRUNC. Our working formula becomes:
=TRUNC(B3)
Figure 11. Output: Extract date from date and time using TRUNC
Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.
Leave a Comment