My Online Training Hub

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

You are here: Home / Excel Formulas / Extract Date or Time from a Date and Time
by

We often need to extract the date or time portion from a date-time serial number for use in other functions.

We can use the INT function to extract the date portion, and the MOD function to extract the time portion.

See examples below.

Extract Date or Time from a Date and Time

The INT function returns the integer or whole number portion of the date time serial number. You can also use the TRUNC function with the same results, but that requires two more characters and we busy Excel users don't have time for that!

The Excel MOD function allows us to retain the decimal or time portion of the date-time serial number.

The syntax for MOD is:

=MOD(number, divisor)

Your date time serial number is the 'number' argument and the divisor is 1. This returns the decimal or time portion of the date-time serial number

Easy!

Want More

Learn more about how Excel handles dates and time in our comprehensive guide to working with Excel Date and Time, or download the files below.

Free eBook - Working with Date & Time in Excel

Everything you need to know about Date and Time in Excel - Download the free eBook and Excel file with detailed instructions.

Enter your email address below to download the comprehensive Excel workbook and PDF.



By submitting your email address you agree that we can email you our Excel newsletter.

More Excel Formulas Posts

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 is a long awaited, new improved way to write, name and store Excel formulas.
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
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 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
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
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
How to identify overlapping dates and times in Excel with a formula that checks a range of cells. Works with Dates and Times.
New Excel Text Functions
TEXTAFTER, TEXTBEFORE and TEXTSPLIT are exciting new Excel Text functions. They’re fairly self-explanatory, however TEXTSPLIT has some cool features.
Take your Excel skills to the next level with this top 10 intermediate Excel functions. These are must know functions for all Excel users.
Category:

Reader Interactions

Leave a Reply

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

Current ye@r *