Cell not holding format.

[jblevins]

Cell C4 and E4 are set to "1:30 PM" The cell is formatted to "Number 2". If I add &" Hr(s)" it truncates it back to 1 decimal place if the result number end in 0.

=IF(AND(C4="",E4=""),"",IF(AND(C4<>"",E4=""),HOUR(NOW()-C4)&"."&MINUTE(NOW()-C4),IF(C4>E4,24-((24*C4)-(24*E4)),((24*C4)-(24*E4))*-1))&" Hr(s)")

 

[RoryA]

As soon as you append text, the cell is text, so number formatting will not apply to it. You'll need to format the number you get using the TEXT function - eg:

TEXT(IF(C4>E4,24-((24*C4)-(24*E4)),((24*C4)-(24*E4))*-1),"0.00")

 

[jasonb75]

Your formula is inconsistent, do you want the results shown as hours and minutes (90 minutes = 1.30 hrs), or decimal hours (90 minutes = 1.5 hrs)?
At the moment it is using decimal hours when E4 is blank, but hours and minutes when E4 is not blank.

I'm looking at it and seeing

=IF(C4="","",MOD(IF(E4="",NOW(),E4)-C4,1))

with a custom format of

[h].mm" Hr(s)"

as a simpler and more consistent alternative.

 

[jblevins]

RoryA, thanks, I have never seen this before, but it works

 

[jblevins]

Your formula is inconsistent, do you want the results shown as hours and minutes (90 minutes = 1.30 hrs), or decimal hours (90 minutes = 1.5 hrs)?
At the moment it is using decimal hours when E4 is blank, but hours and minutes when E4 is not blank.

I'm looking at it and seeing

=IF(C4="","",MOD(IF(E4="",NOW(),E4)-C4,1))

with a custom format of

[h].mm" Hr(s)"

as a simpler and more consistent alternative.

Not looking for time format, looking for quantity of time.

Your formula is inconsistent, do you want the results shown as hours and minutes (90 minutes = 1.30 hrs), or decimal hours (90 minutes = 1.5 hrs)?
At the moment it is using decimal hours when E4 is blank, but hours and minutes when E4 is not blank.

I'm looking at it and seeing

=IF(C4="","",MOD(IF(E4="",NOW(),E4)-C4,1))

with a custom format of

[h].mm" Hr(s)"

as a simpler and more consistent alternative.

Your code returns time and I am looking for quantity. I do like the formatting of " Hr(s)" that you demonstrated.

 

[jasonb75]

What I suggested is quantity of time. If you mean you want decimal rather than proper time, then

=IF(C4="","",MOD(IF(E4="",NOW(),E4)-C4,1)*24)

format as

0.00" Hr(s)"

 

[jblevins]

Who ever the moderator is: There are two solutions here that work -

RoryA​

jasonb75​

I used jasonb75 code because it is less complicated than mine.

 

[jblevins]

What I suggested is quantity of time. If you mean you want decimal rather than proper time, then

=IF(C4="","",MOD(IF(E4="",NOW(),E4)-C4,1)*24)

format as

0.00" Hr(s)"

You really condensed that last piece of code, how about this one.

=IF(AND(B5="",D5=""),"",IF(AND(B5<>"",D5=""),DATEDIF(B5,NOW(),"d"),IF(AND(B5<>"",C5<>"",D5<>"",E5<>"",C5>E5),D5-B5-1,D5-B5)))

 

[jasonb75]

Following a similar logical thought process,

=IF(B5="","",IF(D5="",DATEDIF(B5,NOW(),"d"),D5-B5-(C5>E5)))

The only difference being if D5 is not bank and either C5 or E5 is blank, your formula would have had no valid result and returned FALSE, whist mine will assume that if D5 is not blank, that C5 and E5 will both contain values.

 

[jblevins]

Following a similar logical thought process,

=IF(B5="","",IF(D5="",DATEDIF(B5,NOW(),"d"),D5-B5-(C5>E5)))

The only difference being if D5 is not bank and either C5 or E5 is blank, your formula would have had no valid result and returned FALSE, whist mine will assume that if D5 is not blank, that C5 and E5 will both contain values.

Thanks, worked great.