Forum Discussion
Solved
Using the TIME function in excel
I have a short formula in a cell (Col R) that changes the amount of time someone is entitled to depending upon whether they take leave or not. I want to add additional criteria to the initial formula which acknowledges that the time should reduce to 7 hours if 10 hours are taken in Cols N, O, P or Q. I would also like it to reduce to 7 hours if 5 hours are taken (as in half day leave, TOIL, Comp, etc)
=IF(OR(N34=TIME(10,0,0),O34=TIME(10,0,0),P34=TIME(10,0,0),Q34=TIME(10,0,0),P34=TIME(5,0,0),Q34=TIME(5,0,0)),W34,X34)
You can try an IF(OR( formula.
- Thank you so much for your solution. I had tried it but was missing a crucial )....
- so i was just wondering if you would prefer to SUM those times as in the 10hr could be split across multiple catagories? e.g.:
=IF(OR(SUM(N34:Q34)=TIME(10,0,0),P34=TIME(5,0,0),Q34=TIME(5,0,0)),W34,X34)
btw I copied Quad's formula above so if that was missing a ) then mine might also but I can't find a missing ) in either.
=IF(OR(N34=TIME(10,0,0),O34=TIME(10,0,0),P34=TIME(10,0,0),Q34=TIME(10,0,0),P34=TIME(5,0,0),Q34=TIME(5,0,0)),W34,X34)
You can try an IF(OR( formula.
