Skip to content
xlsoffice. All Rights Reserved
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Lookup and Reference Examples

  • Create hyperlink with VLOOKUP in Excel
  • How to use Excel OFFSET function
  • How to create dynamic named range with OFFSET in Excel
  • Count rows with at least n matching values
  • How to use Excel MATCH Function

Data Analysis Examples

  • Excel Frequency Function Example
  • How To Remove Duplicates In Excel Column Or Row?
  • Excel Pie Chart
  • Move chart to a separate worksheet in Excel
  • Error Bars in Excel

Data Validation Examples

  • Excel Data validation must not contain
  • Excel Data validation date in specific year
  • Excel Data validation only dates between
  • Excel Data validation specific characters only
  • Excel Data validation exists in list

Extract time from a date and time in Excel

by

This tutorial show how to Extract time 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 time portion (the fractional part), you can use a formula that uses the MOD 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 and .5 is the time.

Worked Example:   How to calculate most recent day of week in Excel

 Formula

=MOD(date,1)

Explanation of how this formula works

Assuming A1 contains the date and time value June 1, 2000 12:00 PM, the formula below will return just the time portion (.5):

=MOD(A1,1)

The MOD function returns the remainder from division. The first argument is the number and the second is the divisor. Here are a few examples:

=MOD(5,2) // returns 1
=MOD(7,5) // returns 2

So, if you use MOD with a divisor of 1, the result will be the fractional part of the number, if any, because every whole number can be evenly divided by itself. For example:

=MOD(3.125,1) // returns 0.125

In short, =MOD(number,1) returns just the fractional part of a number, discarding the integer portion, so it’s a convenient way to extract time from a date and time.

Worked Example:   Even and Odd function in Excel

Note: if you use this formula to strip the time from a date + time, you’ll need to adjust the number format to a suitable time format.

Post navigation

Previous Post:

Extract date from a date and time in Excel

Next Post:

Convert text timestamp into 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
  • Remove text by matching in a cell in Excel
  • How to compare two text strings in Excel
  • FIND, FINDB functions: Description, Usage, Syntax, Examples and Explanation
  • Convert text to numbers using VALUE function in Excel
  • How to count specific characters in a cell in Excel
  • Get last weekday in month in Excel
  • Count day of week between dates in Excel
  • NOW function: Description, Usage, Syntax, Examples and Explanation
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • WORKDAY.INTL function: Description, Usage, Syntax, Examples and Explanation
  • PMT function: Description, Usage, Syntax, Examples and Explanation
  • COUPNUM function: Description, Usage, Syntax, Examples and Explanation
  • RECEIVED function: Description, Usage, Syntax, Examples and Explanation
  • ACCRINT function: Description, Usage, Syntax, Examples and Explanation
  • RRI function: Description, Usage, Syntax, Examples and Explanation
© 2022 xlsoffice . All Right Reserved. | Teal Smiles