My Online Training Hub

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

Extract Date or Time from a Date and Time

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.

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Formulas Posts

Excel Formula Checklist
Avoid costly errors with this quick Excel formula checklist. Discover simple tips to prevent mistakes that could impact your work and career.
List Filenames in a Folder
Two easy ways to list file names in a folder in Excel.
Excel REGEX Functions
New Excel REGEX functions make light work of manipulating text and are far easier than the old MID/LEFT/RIGHT/FIND etc. functions.
Modern Excel Functions
10 must know modern Excel functions that simplify common tasks and make you the go-to expert in your office.
Excel GROUPBY and PIVOTBY Functions
Excel GROUPBY and PIVOTBY functions enable you to pivot and summarize your data without the need for PivotTables.
LABS.GENERATIVEAI Function
The Future is Here: Excel's LABS.GENERATIVEAI function brings ChatGPT inside a formula.
Excel SUMPRODUCT Function
The Excel SUMPRODUCT function is one of Excel's most versatile and underutilized functions allowing for flexible aggregations of data.
Summarize Months to Quarters
3 ways (good, better, best) to summarize monthly data into quarters using formulas. Lots of examples and sample file to download.
Excel BYROW and BYCOL Functions
Excel BYCOL and BYROW functions fundamentally change the way we write formulas that calculate across columns and down rows.
python in excel natively
How to use Python in Excel natively using libraries like Pandas, NumPy, Matplotlib, Seaborn and more for analysis and spectacular charts!


Category:

Reader Interactions

Leave a Reply

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

Current ye@r *