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

Data Analysis

  • Conditional Formatting Color Scales Examples in Excel
  • How to calculate average last N values in a table in Excel
  • How to Create Gantt Chart in Excel
  • How to Create Thermometer Chart in Excel
  • Get column index in Excel Table

References

  • Create hyperlink with VLOOKUP in Excel
  • How to use Excel COLUMN Function
  • How to get address of first cell in range in Excel
  • Approximate match with multiple criteria in Excel
  • How to use Excel TRANSPOSE Function

Data Validations

  • Excel Data validation exists in list
  • Excel Data validation must begin with
  • Excel Data validation only dates between
  • Excel Data validation with conditional list
  • Excel Data validation must not contain

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:   Convert time to time zone 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:   Highlight multiples of specific value 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:

Number and Text Filters Examples in Excel

Next Post:

Convert date string to date 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

  • XOR function: Description, Usage, Syntax, Examples and Explanation
  • Extract multiple matches into separate rows in Excel
  • OR function Examples in Excel
  • Return blank if in Excel
  • NOT function: Description, Usage, Syntax, Examples and Explanation

Date Time

  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • How to enter Today’s Date or Static Date and Time in Excel
  • Convert text to date in Excel
  • Get first day of previous month in Excel
  • Get last working day in month in Excel

Grouping

  • Group times into 3 hour buckets in Excel
  • If cell contains one of many things in Excel
  • How to randomly assign data to groups in Excel
  • Group numbers at uneven intervals in Excel
  • Group numbers with VLOOKUP in Excel

General

  • Basic text sort formula in Excel
  • Convert column letter to number in Excel
  • Currency vs Accounting Format in Excel
  • Subtotal invoices by age in Excel
  • AutoRecover file that was never saved in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning