How to Convert Hours to Days in Excel: 6 Effective Methods

Last updated: Apr 16, 2024

The dataset below shows a Project Schedule from beginning to end. Along with it, we can see the Working Hours of each phase in the schedule. As you can see, the hours’ information is not entirely understandable as it’s higher than 24 hours. Therefore, we will convert them to days.

Method 1 – Use CONVERT Function to Convert Hours to Days

Select cell D5 and insert this formula.

=CONVERT(C5,"hr","day")

Press Enter.
You will see the first value of Working Days.

Use the AutoFill tool to apply this formula in cell range D6:D10.

Additional Tip: For other units of time measurement, use these texts for each conversion in formulas.

Year = “yr”
Day = “day”
Hour = “hr”
Minute = “mn”
Second = “sec”

Method 2 – Convert Hours to Days by Combining INT & MOD Functions

Insert a new column titled Additional Hours.

Inset this formula in cell D5:

=INT(C5/24)

Hit Enter and you will see the value in Days.

Insert this formula into E5 to get the remainder.

=MOD(C5/24,1)*24

Hit Enter to get the output.

Select cells D5 and E5 and drag the bottom corner up to cell E10.

Method 3 – Apply FLOOR Function for Converting Hours to Whole Days in Excel

Select cell D5 and insert this formula:

=FLOOR(C5/24,1)

Hit Enter to see the first value of Working Days.

Use the Fill Handle tool to get the result in cell range D6:D10.

Method 4 – Insert QUOTIENT Function for the Conversion of Hours to Days

Type this formula in cell D5:

=QUOTIENT(C5,24)&IF(C5/24<2,"Day","Day")&MOD(C5,24)&IF(MOD(C5,24)<2,"Hour","Hours")

Hit Enter to get the output.

In the formula, we used the IF function to establish the logical condition based on 24 hours on cell C5. We then applied the MOD function to get the remainder from the reference cell. Lastly, we gathered everything with the QUOTIENT function to return the integer position.

Drag the bottom corner of cell D5 up to cell D10 for converting hours to days for each cell.

Additional Tip: Use this formula in case of 8-hours conversions for working days:

=QUOTIENT(C5,8)&"days"&((C5/8)-QUOTIENT(C5,8))*8&"hours"

Method 5 – Convert Hours to Days in Excel with INT Function

Type this formula in cell D5:

=INT(INT(SUBSTITUTE(C5,"hr","")/24))&"days"&""&INT(MOD(SUBSTITUTE(C5,"hr",""),24))&"hours"

Hit Enter. The hours in cell C5 are converted to days and remainder hours like this:

Here, we applied the INT function to get an integer value and the MOD function to find the remainder from cell C5. Along with it, we also used the SUBSTITUTE function to replace text with days and hours.

Use AutoFill to apply the similar formula in cell range D6:D10.

Method 6 – Apply IF Function for Converting Hours to Days

Insert this formula in cell D5:

=IF(MOD(SUBSTITUTE(SUBSTITUTE(C5,"hrs",""),"hr",""),24)>=24,ROUND(SUBSTITUTE(SUBSTITUTE(C5,"hrs",""),"hr","")/24,0),FLOOR(SUBSTITUTE(SUBSTITUTE(C5,"hrs",""),"hr","")/24,1)&"Day "&MOD(SUBSTITUTE(SUBSTITUTE(C5,"hrs",""),"hr",""),24)&"Hours")

Press Enter to get the result.

Here, the IF function defines the condition that cell C5 will show results within 24 hours. Then, the ROUND function rounds the number keeping the remainder with the MOD function. The FLOOR function avoids any fractional values. Lastly, we applied the SUBSTITUTE function for replacing text with time units.

Drag the bottom corner of cell D5 up to cell D10 to get the final conversions.

Things to Remember

You shouldn’t convert the cells into Date format. Otherwise, it will show a false value.
You can apply any of the methods above other than 24 hours as well.

Download Practice Workbook

Download this practice file and try it yourself.

Convert Hours to Days.xlsx

<< Go Back to Time Conversion | Date-Time in Excel | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

4 Comments

nah Apr 12, 2023 at 8:24 PM

This is stupid. If Working hours are 50, than Working Days are not 2. This assumes a work day is 24 hours long. The calculation should consider that a Working day varies from 7-12 hours.
- Reply
  Sanjida Mehrun Guria Apr 13, 2023 at 12:30 PM
  
  Hello Nah,
  Thanks for your feedback. As mentioned in the article, you can try any of the methods for your preferred hours. Only the CONVERT function cannot do that because it is built to perform on standard time format. Therefore, please check the attached Excel file where the working day is considered 8 hours/day.
  https://www.exceldemy.com/wp-content/uploads/2023/04/Convert-Hours-to-Days.xlsx
  Thank you.
  
  Regards,
  Sanjida Mehrun Guria
  Excel VBA & Content Developer
  ExcelDemy
Ula Aug 11, 2023 at 5:43 PM

Hello Sanjida .Thanks for the useful post, appreciated. Could you help with configuring hours data in a table where two units have to be combined to working days, please? EFrequency + unit time in hrs to workdays? Thank you!
- Reply
  Aniruddah Alam Aug 13, 2023 at 12:53 PM
  
  Thanks Ula, for reaching us out. From your comment, what I can understand is that you have Frequency in one column, Unit time in another column, and you want to covert the total hours (Frequency*Time) to working days. Here, I have prepared a dataset similar to your’s need. I consider the average working hours per day to be 8 and put it in cell D4.
  
  Now, to convert the hours to Workdays, we use the following formula
  =QUOTIENT(B7*C7,$D$4)&” Days “&((B7*C7/$D$4)-QUOTIENT(B7*C7,$D$4))*$D$4&” Hours”
  
  Here, we used the 4th method of this article where the QUOTIENT function is used. But there are some modifications in the formula. They are described below:
  B7*C7 is the total hours worked.
  QUOTIENT(B7*C7,$D$4) returns the total whole working days.
  ((B7*C7/$D$4)-QUOTIENT(B7*C7,$D$4))*$D$4& returns the remainder extra hours.
  
  Please remember to use absolute reference to the cell Avg. Workday in Hours cell. Then, you can autofill the rest of the cells.
  I hope it helps.
  Sincerely,
  Aniruddah