• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Extracting Date and Time from a DateTime Value

You are here: Home / Excel Formulas / Extracting Date and Time from a DateTime Value
Extract Date and Time from DateTime Value
September 24, 2019 by Philip Treacy

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.

Excel Date and Time

Calculating Time in Excel

Excel Time Calculation Tricks

Excel Date and Time Formatting

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.

TRUNC function to extract 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.

INT function to extract date

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.

MOD function to extract 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.

TRUNC function to extract 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.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Examples of all functions used in this post are in the sample workbook.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

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

Download the eBook.

Extract Date and Time from DateTime Value

More Excel Formulas Posts

excel formula by example

Excel Formula by Example

Excel can now write a formula by example. Simply give it an example or two of the result and Excel will write the formula.
ai-aided excel formula editor

AI Aided Excel Formula Editor

Save time with this free AI Excel formula editor add-in that writes, edits, improves and interprets formulas for you!
top excel functions for data analysts

Top Excel Functions for Data Analysts

Must know Excel Functions for Data Analysts and what functions you don’t have to waste time learning and why.
excel advanced formula environment

Excel Advanced Formula Environment

Excel Advanced Formula Environment is a long awaited, new improved way to write, name and store Excel formulas.
Pro Excel Formula Writing Tips

Pro Excel Formula Writing Tips

Must know Excel formula writing tips, tricks and tools to make you an Excel formula ninja, including a new formula editor.
excel shaping arrays

New Array Shaping Excel Functions

The Excel Shaping Array Functions makes it easier than ever to reshape arrays and ranges using these purpose built functions
excel nested if functions what not to do

Excel IF Formulas and What Not To Do

Excel IF formulas can get out of hand when you nest too many IFs. Not only do they become unwieldy they’re difficult for anyone to understand
excel image function

Excel IMAGE Function

The Excel IMAGE Function enables you to embed images in a cell using a formula. It supports BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP files

Excel VSTACK and HSTACK Functions

New Excel VSTACK and HSTACK functions makes combining arrays of cells easy and with some clever tricks we can extend their capabilities.
identify overlapping dates and times in excel

Identify overlapping dates and times in Excel

How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.


Category: Excel Formulas
Previous Post:excel data tablesExcel Data Tables
Next Post:Excel Goal SeekExcel Goal Seek

Reader Interactions

Comments

  1. Hugo Boschmans

    September 25, 2019 at 9:32 pm

    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.
    But.
    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.

    Reply
    • Catalin Bombea

      September 25, 2019 at 9:57 pm

      Indeed,
      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.

      Reply
  2. Philip Wiest

    September 25, 2019 at 11:55 am

    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?

    Thanks!

    Reply
    • Philip Treacy

      September 25, 2019 at 12:24 pm

      Hi Philip,

      I missed that one! I’ll add it in.

      It will work too, just another way to get the time.

      Thanks

      Phil

      Reply
  3. Patrick Schommer

    September 24, 2019 at 11:25 pm

    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!

    Reply
    • Philip Treacy

      September 25, 2019 at 9:15 am

      Thanks Patrick, glad this was useful to you 🙂

      Reply

Leave a Reply Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

launch excel macros course excel vba course

Featured Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.