Hi All,

I am task to create a leave system to count the number of leave based on join date.

This is the formula im using

=IF(G9<3,0,IF(F9="M",IF(G9<24,16,IF(G9<=60,18,20)),IF(F9="ESO",IF(G9<24,14,IF(G9<=60,16,18)),IF(F9="TFO",IF(G9<24,10,IF(G9<=60,12,16)),IF(G9<24,8,IF(G9<=60,10,12))))))

Their entitlement is based on their position and length of services as shown in the attachment.

E.G Staff A position is Technician/Foreman/Office Staff = TFO, he work with us since 1 August 2019, his entitlement should be 10.5 .

In a Year we will giving them earn leave, so every 1 month he works he will earn 1 day leave and max is 10.5 for the entire year.

And when he work 2 years but less than 5 years he will be entitled to 16 days the next year.

Can i ask if there is any way to formula it to achieve such condition?

I am task to create a leave system to count the number of leave based on join date.

This is the formula im using

=IF(G9<3,0,IF(F9="M",IF(G9<24,16,IF(G9<=60,18,20)),IF(F9="ESO",IF(G9<24,14,IF(G9<=60,16,18)),IF(F9="TFO",IF(G9<24,10,IF(G9<=60,12,16)),IF(G9<24,8,IF(G9<=60,10,12))))))

Their entitlement is based on their position and length of services as shown in the attachment.

E.G Staff A position is Technician/Foreman/Office Staff = TFO, he work with us since 1 August 2019, his entitlement should be 10.5 .

In a Year we will giving them earn leave, so every 1 month he works he will earn 1 day leave and max is 10.5 for the entire year.

And when he work 2 years but less than 5 years he will be entitled to 16 days the next year.

Can i ask if there is any way to formula it to achieve such condition?