Convert hours:minutes into days, hours, minutes

  • Thread starter Thread starter Chinny
  • Start date Start date
C

Chinny

How can I convert [h]:mm into days, hours, minutes? Or even minutes int
days, hours, minutes (where there are lots of minutes...)

Where a day = 7.5 hours
 
Thanks Frank,

The problem I have is as follows.

I have a spread sheet as follows

Column A = lists various tasks
eg - = Test entry

Column B = you enter the frequency of each of the tasks per day.
Cell format "number"
eg - = 10

Column C = you enter how long each respective task takes to perform
Cell format = "number", if it takes you 0hrs 13 mns, you'd enter "13
(easier to enter)
eg - = 13

Column D = calculates how much time per day (24hr day) each task i
taking.
=SUM(TEXT(CONVERT(C2,"mn","hr")/24,"h:mm")*'Initial Entry'!B2)
Cell format [h]:mm

eg - =SUM(TEXT(CONVERT(13,"mn","hr")/24,"h:mm")*10)
=2:10

Column E = calculates how much time per wk each task is taking. Week
five days
=SUM(D2)*5
Cell format = [h]:mm

eg - =2:10*5
=10:50

Column F = calculates how much time per mnth each task is taking. Mont
= 20 Days
=SUM(D2)*20
Cell format = [h]:mm
eg - =2:10*20
=43:20

Column G = calculates how much time per yr each task is taking. Year
260 days. = 52weeks*5day
=SUM(D2)*260
Cell format = [h]:mm
eg - =2:10*260
=563:20

Column H = Will just be a reformat of Column G eg - I want to show thi
in the following format - #days,#hrs,#mns. Where a day = 7.5 workin
hrs.

The problem I have is that if I use the following formula

=TEXT(CONVERT(SUM(D2)*260,"mn","hr")/7.5,"d:h:mm")

it truncates the hrs and doesn't convert them over to days

and gives =0:1:15, which is clearly incorrect. I have even trie
customising the format of the cell manually to "dd:hh:mm" but this ha
no affect.

Can this be acheived? or will the hrs always bve truncated, and no
shifted into days
 
Hi
the problem is you're creating strings in your formulas. This will make
it quite complicated for you to sum them. Try the following formulas
(hope I got your layour correctly):
B2: no change
C2: I'd suggest you enter this as valid time (would make everything
easier) but if you want just enter this as minutes
D2: Use the formula
=B2*(C2/(24*60))
and format this cell as [h]:mm

E2: No need for using SUM. Simply use
=D2*5
and format this cell as [h]:mm

F2: Formula
=D2*20
and format this cell as [h]:mm

G2: Formula
=D2*260

H2: One way: Formula
=G2
and format this with the custom format 'D hh:mm'

Or use the following formula
=INT(G2/24) & "days and " & TEXT(MOD(G2,24),"hh:mm")
 
Back
Top