Customized cell formatting

A

AH

Hi,

Does anybody have any idea how to use cell formatting to show times like
this. One day should be 8 hours long (like normal working time). So any
values below 1/3 should be show like normal hours and minutes, but when this
is exceeded, the first value should indicate working days.

An example shows what I mean:

0.25 => 6:00
0.5 => 1 day 4:00
1.0 => 3 days

and so on.

Thanks in advance,

Asser
 
S

Stephen Bye

I can't find a simple way to do it with cell formatting, but you could do it
like this in an adjacent cell (where A2 contains the 0.25 etc. value):

=TEXT(INT(A2*3),"[<1]"""";[<2]0 ""day "";#0 ""days """) &
TEXT(MOD(A2*3,1)/3,"h:mm;;")
 
A

AH

Thank you for your time Stephen.

I though it too that it wouldn't be possible. I just wanted to be sure. I'll
use your nice formula.

Thanks again,
Asser
 
Top