How do I convert hours to days?

W

WBJ

I am entering start-times and end-times (cols. A & B) and calculating the
duration (col B - col A) in C. I want to format the sum of all durations --
28:30, for instance (28 hours, 30 minutes) for an 8-hour day as 3 days, 4
hours, 30 minutes.

Any ideas?
 
S

Steven

Assuming the sum of all durations in cell A1
To return the value of days =DAY(A1*3)
To return number of remaining hours =(+A1*3-DAY(A1*3))*8
To return minutes = MINUTE(A1)

If you wish you can display all 3 formula together in one cell by
joining them with
=DAY(A1*3)&" days "&(A1*3-DAY(A1*3))*8&" hours "&MINUTE(A1)&" minutes"
Let me know if this helps
 
S

Steven

Thanks for tweeking the HOURS formula - so much smoother than mine.
I see the problem with the DAY formula if the value >31 days but I
don't understand the MOD formula will yield the correct answer.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Subtract times? 2
Convert a number to Hours, Minutes, etc. 3
Converting Hours to Decimal 7
Convert hours to days 8
Working with duration data 3
Display Sum of hours as an equivalent in days and hours 2
minutes seconds days weeks years 2
How do I get total amount of hours in time format 3

Top