annual leave days formula

Visitor

hi 

this is for calculating annual leave days deserved

if G1>=12 (months) THEN G1*2,5
IF 6 (months)<=G1<12 (months) THEN G1*2

how can i combine in 1 formula, it is working for one condition not for the other.

thanks

2 Replies

Cannot work out whether you are doing VBA or Excel. Either language requires an AND.

  • VBA: IF 6 <= G1 AND G1 < 12 THEN ...
  • Excel: =IF(AND(6<=G1;G1<12); ...

There should be clear on after completion of 12 months 2.5 days then completion of 6 months 2 days,(It is considered that leave is eligible only after completion of 6 months)
=IFS(G1>=12,G1*2.5,G1>=6,G1*2,TRUE,0)
=IF(G1>=12,G1*2.5,IF(G1>=6,G1*2,0))

Months IFS IF
12 30 30
7 14 14
5 0 0

=IFS(A2>=12,A2*2.5,A2>=6,A2*2,TRUE,0)
=IF(A2>=12,A2*2.5,IF(A2>=6,A2*2,0))