Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Use Data Form to input, edit and delete records in Excel
  • Conditional Formatting Data bars Examples in Excel
  • How to create running total in an Excel Table
  • How to Create Thermometer Chart in Excel
  • Conditional Formatting Color Scales Examples in Excel

References

  • Count unique text values with criteria
  • How to get address of last cell in range in Excel
  • How to use Excel VLOOKUP Function
  • How to use Excel TRANSPOSE Function
  • How to use Excel OFFSET function

Data Validations

  • Excel Data validation allow uppercase only
  • Excel Data validation don’t exceed total
  • Excel Data validation unique values only
  • Excel Data validation allow weekday only
  • Excel Data validation must begin with

Extract date from a date and time in Excel

by

This tutorial show how to Extract date from a date and time in Excel using the example below.

If you have dates with time values and you want to extract only the date portion, you can use a formula that uses the INT function.

Note: Excel handles dates and time using a scheme in which dates are serial numbers and times are fractional values. For example, June 1, 2000 12:00 PM is represented in Excel as the number 36678.5, where 36678 is the date portion and .5 is the time portion.

Worked Example:   INT function: Description, Usage, Syntax, Examples and Explanation

Formula

=INT(date)

Explanation of how this formula works

So, assuming A1 contains the date and time, June 1, 2000 12:00 PM, the formula below returns just the date portion (36678):

=INT(A1)

The time portion of the value (the fractional part) is discarded. You could also use the TRUNC function with the same result:

=TRUNC(A1)

Notes:

Worked Example:   Get age from birthday in Excel

1. With either method above, make sure you use a date format on the result that does not include a time. Otherwise, you’ll see the time displayed as 12:00 AM.

Worked Example:   Use of INT and TRUNC function in Excel

2. For dates and times (which must be positive in Excel) there is no difference in using INT and TRUNC to extract an integer. But INT actually rounds numbers down to the nearest integer, which makes a difference when values are negative.

Post navigation

Previous Post:

Dynamic date list in Excel

Next Post:

Extract time from a date and time in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • FALSE function: Description, Usage, Syntax, Examples and Explanation
  • SWITCH function: Description, Usage, Syntax, Examples and Explanation
  • IF with wildcards in Excel
  • SWITCH function example in Excel
  • How to return blank in place of #DIV/0! error in Excel

Date Time

  • Extract time from a date and time in Excel
  • Calculate number of hours between two times in Excel
  • Get first day of previous month in Excel
  • Sum through n months in Excel
  • NETWORKDAYS function: Description, Usage, Syntax, Examples and Explanation

Grouping

  • Map text to numbers in Excel
  • Calculate conditional mode with criteria in Excel
  • How to randomly assign data to groups in Excel
  • Group arbitrary text values in Excel
  • If cell contains one of many things in Excel

General

  • Customize Ribbon In Excel
  • How to count total number of cells in a rectangular range in Excel
  • How to calculate percentage of total in Excel
  • Count cells that do not contain errors in Excel
  • How to calculate percent change in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning