Display hours over 8 hours as 1 day

[jeffreybrown]

So far this is as far as I have gotten which will work fine for over 24 hours, but since our day is 8 hours, how can this read 3d 00:50:00 for a time that is 24:50:00?

Any thoughts?

=IF(SUMPRODUCT(D16:F16,G16:I16)/1440>TIME(8,0,0),INT(SUMPRODUCT($D$16:$F$16,$G$16:$I$16)/1440)&"d "&TEXT(SUMPRODUCT($D$16:$F$16,$G$16:$I$16)/1440,"hh:mm:ss"),SUMPRODUCT($D$16:$F$16,$G$16:$I$16)/1440)

 

[jeffreybrown]

Anybody have any thoughts?

 

[barry houdini]

So the SUMPRODUCT gives you a number of minutes and you want to convert that to workdays? Would 1000 minutes therefore be 16 hours 40 mins and become 2d 00:40:00? If so try

=TEXT(INT(SUMPRODUCT(D16:F16,G16:I16)/1440/"8:00"),"0\d ;;")&TEXT(MOD(SUMPRODUCT(D16:F16,G16:I16)/1440,"8:00"),"hh:mm:ss")

 

[jeffreybrown]

Yes Barry that is exactly right and this is perfect...

 

[Beate Schmitz]

I canceled my answer as I misunderstood your question.