Forum Discussion

Piero_VDP's avatar
Piero_VDP
Copper Contributor
May 09, 2020

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 cal...
excel
Formulas and Functions
Rajesh_Sinha's avatar
Rajesh_Sinha
Iron Contributor
Sep 24, 2020

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.

Resources