# Cell not holding format.

#### jblevins

##### Board Regular
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.
Excel Formula:
``=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)")``

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### RoryA

##### MrExcel MVP, Moderator
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

##### Well-known Member
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
Excel Formula:
``=IF(C4="","",MOD(IF(E4="",NOW(),E4)-C4,1))``
with a custom format of
Excel Formula:
``[h].mm" Hr(s)"``
as a simpler and more consistent alternative.

#### jblevins

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

#### jblevins

##### Board Regular

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
Excel Formula:
``=IF(C4="","",MOD(IF(E4="",NOW(),E4)-C4,1))``
with a custom format of
Excel Formula:
``[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
Excel Formula:
``=IF(C4="","",MOD(IF(E4="",NOW(),E4)-C4,1))``
with a custom format of
Excel Formula:
``[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

##### Well-known Member
What I suggested is quantity of time. If you mean you want decimal rather than proper time, then
Excel Formula:
``=IF(C4="","",MOD(IF(E4="",NOW(),E4)-C4,1)*24)``
format as
Excel Formula:
``0.00" Hr(s)"``

#### jblevins

##### Board Regular

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

#### jasonb75​

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

#### jblevins

##### Board Regular
What I suggested is quantity of time. If you mean you want decimal rather than proper time, then
Excel Formula:
``=IF(C4="","",MOD(IF(E4="",NOW(),E4)-C4,1)*24)``
format as
Excel Formula:
``0.00" Hr(s)"``
Excel Formula:
``=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

##### Well-known Member
Following a similar logical thought process,
Excel Formula:
``=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

##### Board Regular
Following a similar logical thought process,
Excel Formula:
``=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.

Last edited:

Replies
7
Views
261
Replies
2
Views
277
Replies
0
Views
245
Replies
3
Views
136
Replies
7
Views
204

1,147,743
Messages
5,742,943
Members
423,765
Latest member
PaulD1984

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back