Forum Discussion

sarahn1985's avatar
sarahn1985
Copper Contributor
Jul 13, 2022

annual leave days formula

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

excel
Formulas and Functions
  • sivakumarrj's avatar
    sivakumarrj
    Brass Contributor
    Jul 13, 2022

    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))

  • ecovonrein's avatar
    ecovonrein
    Iron Contributor
    Jul 13, 2022

    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); ...

Resources