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

Lookup and Reference Examples

  • Approximate match with multiple criteria in Excel
  • INDEX function: Description, Usage, Syntax, Examples and Explanation
  • How to use Excel ROWS Function
  • How to use Excel LOOKUP Function
  • How to calculate two-way lookup VLOOKUP in Excel Table

Data Analysis Examples

  • Everything about Charts in Excel
  • How to Create One and Two Variable Data Tables in Excel
  • How to count table columns in Excel
  • Conditional Formatting Color Scales Examples in Excel
  • Move chart to a separate worksheet in Excel

Data Validation Examples

  • Excel Data validation exists in list
  • Excel Data validation no punctuation
  • Excel Data validation must not contain
  • Excel Data validation unique values only
  • Prevent invalid data entering in specific cells

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:   Create date range from two dates 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:   How to return TRUE when all cells in range are blank 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:   How to count total characters in a range 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
  • How to strip protocol and trailing slash from URL in Excel
  • How to strip non-numeric characters in Excel
  • Add line break based on OS in Excel
  • TEXTJOIN function: Description, Usage, Syntax, Examples and Explanation
  • How to extract name from email address in Excel
  • Sum through n months in Excel
  • Generate series of dates by weekends in Excel
  • Display Days in month in Excel
  • WEEKDAY function: Description, Usage, Syntax, Examples and Explanation
  • Get last working day in month in Excel
  • How to calculate annuity for interest rate in excel
  • TBILLYIELD function: Description, Usage, Syntax, Examples and Explanation
  • Calculate interest rate for loan in Excel
  • XIRR function: Description, Usage, Syntax, Examples and Explanation
  • DISC function: Description, Usage, Syntax, Examples and Explanation
Acronyms, Abbreviations, Initialism & What They Stand For
© 2022 xlsoffice . All Right Reserved. | Teal Smiles