Convert hours:minutes into days, hours, minutes

[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

 

[Frank Kabel]

Hi
as decimal notion try
=(A1*24)/7.5

 

[Chinny]

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

 

[Frank Kabel]

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")