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

Data Analysis

  • Calculate Conditional Percentile ‘IF’ in table in Excel
  • How to Create Column Chart in Excel
  • Conditional Formatting Icon Sets Examples in Excel
  • Randomize/ Shuffle List in Excel
  • Remove Duplicates Example in Excel

References

  • How to create dynamic named range with OFFSET in Excel
  • Two-way lookup with VLOOKUP in Excel
  • How to use Excel MATCH Function
  • How to get first row number in range in Excel
  • Lookup entire row in Excel

Data Validations

  • Excel Data validation must contain specific text
  • Excel Data validation specific characters only
  • Excel Data validation with conditional list
  • Excel Data validation whole percentage only
  • Excel Data validation date in next 30 days

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.

 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.

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:

COSH function: Description, Usage, Syntax, Examples and Explanation

Next Post:

Excel Data validation require unique number

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

  • How to return blank in place of #DIV/0! error in Excel
  • How to use Excel XOR Function
  • NOT function: Description, Usage, Syntax, Examples and Explanation
  • IFNA function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel TRUE Function

Date Time

  • Add workdays to date custom weekends in Excel
  • EDATE function: Description, Usage, Syntax, Examples and Explanation
  • DAYS function: Description, Usage, Syntax, Examples and Explanation
  • TIME function: Description, Usage, Syntax, Examples and Explanation
  • Display the current date and time in Excel

Grouping

  • Group numbers at uneven intervals in Excel
  • Group arbitrary text values in Excel
  • Categorize text with keywords in Excel
  • How to randomly assign people to groups in Excel
  • How to randomly assign data to groups in Excel

General

  • Select, Insert, Rename, Move, Delete Worksheets in Excel
  • Subtotal by color in Excel
  • Convert column number to letter in Excel
  • How to test a range for numbers in Excel
  • How to calculate percentage discount in Excel
© 2025 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning