Working out Overtime

[ifanew]

Hi

I'm trying to express the number of overtime hours into the number of work days but am bogged down in time formats and formulas and getting nowhere.

The example I'm trying to work with is a figure of 90 hours which I want broken down in to the number of work days (plus additional hours and minutes) given that a work day has a value of 7 hours 24 minutes. I know that the result I should be getting is 12 days (of 7h24m) and 1h 12m remainder.

When I started I though it would be simple, and it probably is, but at the moment it's just boiling my brain.

Any ideas would be very welcome.

Thanks

 

[Comfy]

If you format your value for total overtime hours as General what number does it give you?

 

[konew1]

if the total o/t hours is in A1 then the days is ROUNDDOWN((A1/24)/((7/24)+(1/60)),0)
and the hours ROUNDDOWN(24*MOD((A1/24)/((7/24)+(1/60)),1)*((7/24)+(1/60)),0)
and the minutes MOD(24*MOD((A1/24)/((7/24)+(1/60)),1)*((7/24)+(1/60)),1)*60

These can all be simplified. I Left all the factors in to show the calcs - all based on 1/24 being 1 hour, and 1/24/60 being one minute

 

[ifanew]

Absolutely brilliant just what I was after, many thanks Konew, now all I need to do is understand it.