HomeGoogle DocsSpreadsheet

Converting Time Duration to Day, Hour, and Minute in Google Sheets

Spreadsheet

Published on

Prashanth KV
By Prashanth KV
Modified date:

In this tutorial, you will learn how to convert time duration to day, hour, and minute in Google Sheets. Before we get started, let’s understand how to find time duration in Google Sheets.

A few months ago, I wrote a tutorial on how to calculate working hours, titled Payroll Hour Calculation.

In that tutorial, I only mentioned how to calculate the time duration between two times on the same day. This is because this is what we usually want to do when we want to find the total hours worked in a day.

Hours Worked in a Day (Decimal Time) from Start and End Times

We use decimal time in payrolls because it is easier to calculate wages using decimal numbers.

Formula 1:

=(HOUR(B2)+MINUTE(B2)/60)-(HOUR(A2)+MINUTE(A2)/60)
time duration in a day in Google Sheets

The above example shows how to use Time functions to find the total hours and minutes worked from start and end times in Google Sheets. We omitted the seconds in the formula.

Here is an alternative formula that includes the seconds also:

Formula 2:

=(B2-A2)*24

I recommend this formula #2 for returning decimal time duration from a start and end time in Google Sheets.

However, you must format the result to Format > Number > Duration before you can use it in other calculations. Alternatively, you can use the TO_PURE_NUMBER function like TO_PURE_NUMBER((B2-A2)*24).

Time duration is the difference between two times. It will be in the “Hours:Minutes:Seconds” format, e.g., B2-A2, where B2 and A2 contain start and end times, respectively. No need to multiply the result by 24.

Our topic is converting time duration to day, hour, and minute in Google Sheets. I’ll come to that after explaining the night shift hours calculation.

You can use the above two formulas to get the total decimal hours between two times. However, the start and end times must be within the same day.

So, it may not be quite useful when you have to calculate the night shift hours.

Then, how do you find the night shift working hours in Google Sheets?

Night Shift Hours (Decimal Time) Calculation in Google Sheets

You can use a logical IF statement with the above formulas when you want to perform the night shift hours calculation in Google Sheets.

Find Night Shift Hours in Google Sheets

You can use this time formula =if(B3<A3,(B3-A3+1)*24,(B3-A3)*24) to calculate night shift hours correctly in Google Sheets.

What does this formula do?

The logical IF formula tests the time in B3 first. If the end time (B3) is less than the start time (A3), it adds 1 (24 hours) to B3. This is because the start time of a night shift is typically the next day, so the formula needs to account for this.

If you don’t want to use the IF logical test, you can add corresponding dates to times.

For example, if the start time is 8:00 AM on March 17th and the end time is 5:00 AM on March 18th, you would enter the following formula in cell C4:

=(B4-A4)*24

Hours Worked as Duration

Before converting time duration to day, hour, and minute, we need to know how to get it in Google Sheets. I mentioned it above.

Please see the last formula (C4) in the above screenshot:

=(B4-A4)*24

The result is formatted as a number. You can also use the following formula:

=(B4-A4)

In this case, you need to go to cell C4 and apply the Format > Number > Duration custom number format. This will display the result as 21:00:00 (time duration) instead of 21 (decimal duration).

I hope this helps! Let’s move on to our main topic now.

How to Convert Time Duration to Day, Hour, and Minute in Google Sheets

Below you can find all the details you need about time duration calculation in Google Sheets.

The duration between two times is easy to find in Google Sheets. Simply subtract one time from another, then apply the duration number format by going to Format > Number > Duration.

Quickest way to find time duration in Google Sheets

Please refer to cell C6. I want to extract the number of days from this time duration result.

That means I want to convert the time duration to day, hour, and minute in Google Sheets. Here is the formula:

Formula to Convert Time Duration to Days, Hours, and Minutes in Google Sheets:

=int(C2)&" day(s) "&hour(mod(C2,1))&" Hour(s) "&Minute(mod(C2,1))&" Minutes(s)"
Example to find Day, Hours, Minute from Google Sheets Time Duration

This formula extracts the day, hour, and minutes from the time duration and joins them using the ampersand.

Would you like to get the days, hours, and minutes from the time duration in separate cells?

The Hour(), Minute(), Int(), and Mod() functions help us to convert the time duration to day, hour, and minute in Google Sheets.

Update:

If you want to convert time duration to 8 hours day, hour, and minute, follow this syntax:

=int((duration*24)/8)&" day(s) "&hour(mod(duration*24,8)/24)&" Hour(s) "&Minute(mod(duration*24,8)/24)&" Minutes(s)"

You can replace duration with C2 to test this syntax.

INT Function to Return the Number of Days

You can use the INT function to retrieve the date from a timestamp.

INT rounds a number down to the nearest integer. So, it returns the date part of the timestamp.

Note: In Google Sheets, dates are stored as serial numbers while times are stored as decimal fractions of a day.

When you use it with time duration, it returns the number of days in the duration.

Please see column C in the above example, where I have used a formula to find the time duration.

For example, the duration in cell C5 is 72:00:00. The following code will return 3, i.e., three days.

=INT(C5)

Now we want to find the time. For that, we can use MOD.

MOD to Return the Time from Duration

Don’t confuse the MOD function with the MODE function. They are two different Google Sheets functions.

We have converted the time duration to the number of days. What about the hours and minutes?

The INT function returns the number of days. So, duration - INT(duration) will be the time component.

Example:

Extract Time for Time Stamp in Google Sheets

We can simplify this with the following alternative formula.

=MOD(C7,1)

To get the hours and minutes, wrap the above MOD formula with the HOUR and MINUTE functions, respectively. That’s all.

Conclusion

I hope this tutorial has simplified converting time duration to day, hour, and minute in Google Sheets.

Related:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.
Excel Formula

How to Create a Searchable Table in Excel Using the FILTER Function

Finding specific records, or rows containing the required information, is straightforward in Excel using...
Excel Formula

Time Sequences in Excel by Minute, Hour, or Second Increments

Creating time sequences, whether by hour, minute, or second increments in Excel, can be...
Excel Formula

Finding Most Frequent Text in Excel with Dynamic Array Formulas

Looking to identify the most frequently occurring text in Excel? You can do this...
Excel Formula

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

More like this

Spreadsheet

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...
Spreadsheet

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...
Spreadsheet

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

12 COMMENTS

  1. Hello,

    Is there a solution to convert a duration into a number of working days (8h vs 24h)? I tried the above formula, but it doesn’t provide a realistic period to complete a task.

    Example:

    The task is 300h, and the formula then calculates that it takes 12 days, 12 hours.

    Based on the usual working days of 8h, this task would take 37.5 days to complete.

    Is there a formula for this?

    Thanks in advance for your help!!!

    • Hi Julien,

      You can try this formula:

      =ROUND(TO_PURE_NUMBER((C2*24)/8),2)

      Where C2 contains the time duration.

      I’ve also updated the tutorial.

      I hope this helps!

  2. You could build the elements to produce time, i.e., A1 has time in HH: MM and A2 has decimal time.

    To convert HH:MM to decimals: =hour(A1)+(minute(A1)/60)

    To convert decimals to HH: MM =time(trunc(A2,0),(A2-trunc(A2,0))*60,0)

  3. Thanks! I’m using your formula with ArrayFormula to fill the entire column. This slows the sheet down. What’s the maximum number of rows you would recommend using this with?

    Here’s the formula:

    =ArrayFormula(int(now()-D2:D)&" d "&hour(mod(now()-D2:D,2))&" h "&minute(mod(now()-D2:D,2))&" m")

    • Hi, John Horning,

      NOW() is a volatile function. So it will definitely slow down your sheet’s performance.

      Another point I have noted is the lack of using LEN or ISBLANK to remove empty cells.

      Eg.:

      =ArrayFormula(if(len(D2:D),(int(now()-D2:D)&" d "&hour(mod(now()-D2:D,2))&" h "&minute(mod(now()-D2:D,2))&" m"),))

  4. Really like your style. Everything is clear and simple – and so useful!!

    One thing you could make better is to attach the filled google sheet, even in read-only mode.
    Besides I like articles where the author explains a problem and propose the solution. You’ve got that!

    Thanks

  5. Hi Prashanth,
    Is there any way to convert the total number of seconds (elapsed seconds) to time duration?
    I have production data, based on the cycle time of each part in seconds and quantity to produce, I am getting the total number of seconds required to complete the production. I want to convert that to the duration in [HH]:MM:SS format. Tried a lot but not successful.
    For e.g. for 20,000 parts with a cycle time of 30 seconds. It will take 6,00,000 seconds that is 166.667 hrs (seconds/3600) but I want in [HH]:MM:SS.

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.