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)
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)*24I 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.
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)*24The 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.
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)"
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:
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:
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!
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)Hi, Cameron,
Another way to do the same.
=A2/24Apply Format > Number > Time
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"),))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
Hi, Łukasz,
Thanks for your feedback!
I have started including example sheets in my new posts, though not in all.
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.
Dividing seconds by 86400 and changing the format to [HH]:MM:SS gives the output. but is there any better way?
Hi, Aditya Darekar,
That formula won’t be enough. See if this helps – How to Convert Seconds to HH:MM:SS Format in Google Sheets.
Hi, Aditya Darekar,
That formula won’t be enough to convert. See if this tutorial helps – How to Convert Seconds to HH:MM:SS Format in Google Sheets.