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

Lookup and Reference Examples

  • Left Lookup in Excel
  • How to use Excel MATCH Function
  • Get nth match with INDEX / MATCH in Excel
  • Last row number in range
  • How to get last row in text data in Excel

Data Analysis Examples

  • Randomize/ Shuffle List in Excel
  • Create Scatter Chart in Excel
  • How to perform a t-Test in Excel
  • How to create Checklist in Excel
  • How to combine 2 or more chart types in a single chart in Excel

Data Validation Examples

  • Excel Data validation number multiple 100
  • Excel Data validation whole percentage only
  • Excel Data validation must begin with
  • Excel Data validation no punctuation
  • Excel Data validation date in specific year

Get days, hours, and minutes between dates in Excel

by

To calculate and display the days, hours, and minutes between two dates, you can use the TEXT function with a little help from the INT function. Alternatively, you can adapt the formula using SUMPRODUCT.

Formula

=INT(end-start)&" days "&TEXT(end-start,"h"" hrs ""m"" mins """)

Explanation

In the example shown, the formula in D5 is:

=INT(C5-B5)&" days "&TEXT(C5-B5,"h"" hrs ""m"" mins """)

How this formula works

Most of the work in this formula is done by the TEXT function, which applies a custom number format for hours and minutes to a value created by subtracting the start date from the end date.

Worked Example:   Sum by weekday in Excel
TEXT(C5-B5,"h"" hrs ""m"" mins """)

This is an example of embedding text into a custom number format, and this text must be surrounded by an extra pair of double quotes.

The value for days is calculated with the INT function, which simply strips off the integer portion of the end date minus the start date:

INT(C5-B5)

Although you can use “d” in a custom number format for days, the value will “roll over” back to zero when days is greater than 31.

Worked Example:   Sum bottom n values in Excel

Include seconds

To include seconds, you can extend the custom number format like this:

=INT(C5-B5)&" days "&TEXT(C5-B5,"h"" hrs ""m"" mins ""s"" secs""")

Total days, hours, and minutes between dates

To get the total days, hours, and minutes between a set of start and end dates, you can adapt the formula using SUMPRODUCT like this:

=INT(SUMPRODUCT(ends-starts))&" days "&TEXT(SUMPRODUCT(ends-starts),"h"" hrs ""m"" mins """)

where “ends” represents the range of end dates, and “starts” represents the range of start dates. In the example shown, D11 contains this formula:

=INT(SUMPRODUCT(C5:C9-B5:B9))&" days "&TEXT(SUMPRODUCT(C5:C9-B5:B9),"h"" hrs ""m"" mins """)
Worked Example:   Count holidays between two dates in Excel

Post navigation

Previous Post:

Get last working day in month in Excel

Next Post:

Get days between dates 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
  • Count Number of Words in Excel
  • How to strip numeric characters from cell in Excel
  • How to strip html from text or numbers in Excel
  • How to count total characters in a range in Excel
  • How to create email address from name in Excel
  • Generate series of dates by weekends in Excel
  • How to calculate Day of the Year in Excel
  • ISOWEEKNUM function: Description, Usage, Syntax, Examples and Explanation
  • How to show last updated date stamp in Excel
  • Convert date string to date time in Excel
  • PPMT function: Description, Usage, Syntax, Examples and Explanation
  • Future value vs. Present value examples in Excel
  • PRICEDISC function: Description, Usage, Syntax, Examples and Explanation
  • IRR function: Description, Usage, Syntax, Examples and Explanation
  • COUPPCD function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2021 xlsoffice. All Rights Reserved | Teal Smiles