Converting work hours to day/hours/minutes

[WxmanPrice]

I am trying to display various work hours/shifts as days:hours:minutes.

For example 457.50 hours or 457:30 would be displayed as 61:00:00 or just 61
days (doesn't matter which is displayed whatever is easier) based on a 7.5
hour work day.

I'd also like to have 2 additional columns. The next would be the above
based on a 9.0 hour work day and the other based on a 9.5 hour work day. The
9.5 work day would be displayed as either 48:01:30 (all I can get it to show
is 48:09).

Any thoughts?

 

[Luke M]

Could do something like:

=INT($A$2/7.5)&":"&TEXT(MOD($A$2/7.5,1),"hh:mm")

Change the divisors as required to meet your hours per workday. Note that
this formula returns a text string, not a value.

 

[EricG]

Here's an ugly formula that seems to work. Change the "9.5" to whatever you
need.

=INT($A2/9.5)&":"&INT(($A2-INT($A2/9.5)*9.5))&":"&($A2-INT($A2/9.5)*9.5-INT(($A2-INT($A2/9.5)*9.5)))*60

HTH,

Eric

 

[Teethless mama]

=INT(A1/9.5)&":"&TEXT(MOD(A1/9.5,1)*9.5/24,"hh:mm")