Forum Discussion
Formula If, Time calculations
Good morning,
I have an issue with formula 'if' with time calculations.
I hope somebody can help me.
This is the case (see file in attachment :
I know you would probably make all these calculations 1 formula, but that is too difficult for me. Is not a problem either, I do it in steps and that works fine, but I now get stuck on the following:
- If you go to cell B 56 you will see the following formula: =ALS(N47=$C$45;"0:00";C38+($C$26-N47)). So if I entered it correctly then I ask to enter 0:00 if the value in N47 = to the value in C45 and if it is not same then c38 should be increased by C26-N47. (By the way, the value in N47 does not change until a time is entered in B47)
- What's my problem: it doesn't recognize the value in N47 (0:00) as the same one in C45 (0:00). I think this has to do with the fact that the value in N47 is the result of a formula =ALS(B47=$C$45;"0:00";$E$47-B47-K47) (by the way this formula works which does not refer to cells whose value is the result of a formula.)
I hope that somebody can help, tell me what I am doing wrong, or how I can work around this issue. I thank you in advance for you efforts.
Greetings Piero
3 Replies
Piero_VDP ,,
Your basic mistake is that, everywhere you are using TIME as TEXT !
Your formula should written like this :
In Range B56:B60 =IF(N47=$C$45,0,C38+($C$26-N47)) In Range H56:H59 =IF($F$47>=B56,0,TIMEVALUE("0:15")) In Range N47:N52 =IF(B47=$C$45,0,$E$47-B47-K47) In Range H47:H52 =IF($F$47<B47,0,TIMEVALUE("0:15")) Accordigly edit the formula in I47:J52 also.- Since you have formatted cells as TIME 0:00 then, if formula return ZERO, Excel converts into 0:00.
- Excel reads TIMEVALUE("0:15") , as Time, but not as Text.
- Check the attached Workbook.
Piero_VDP , below formula shall work fine for B56 cell
=IF(TIMEVALUE(N47)=$C$45,"0:00",C38+($C$26-N47))
Basically,
1) if cell format is TIME, then EXCEL recognizes cell value as "HH:MM:SS AM/PM". So even though cell value apears to eyes as "0:00" , but EXCEL interprets it as "12:00:00 AM"
2) If cell format is GENERAL or TEXT , or as in your case it is a "text" result of another formula, then EXCEL interprets text as text. But this text "0:00" is not same as time "0:00"
3) Care to learn about how TIMEVALUE function works. It converts the text time to "TIME" time.
Knowing above 3 concepts, you would understand how the solution given above works, and how you can take care about dealing with time calculations in future.
