Cell not holding format.

jblevins

Board Regular
Joined
Sep 2, 2013
Messages
228
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
Joined
May 2, 2008
Messages
37,764
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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
Joined
Dec 30, 2008
Messages
14,515
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 2, 2013
Messages
228

ADVERTISEMENT

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
Joined
Dec 30, 2008
Messages
14,515
Office Version
  1. 365
Platform
  1. Windows
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)"
 
Solution

jblevins

Board Regular
Joined
Sep 2, 2013
Messages
228

ADVERTISEMENT

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

Board Regular
Joined
Sep 2, 2013
Messages
228
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)"
You really condensed that last piece of code, how about this one.
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
Joined
Dec 30, 2008
Messages
14,515
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Sep 2, 2013
Messages
228
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:

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top