find annual leave

[sna]

Hi Dear!
How are you doing? Hope you're doing well and Stay safe.
I want to see the place of earn annual leave release a total of the date that they join work.
Below is the condition:
if staff join under or equal 1-January-2021 will get 1.5 days for annual leave automatically until December will get 18 days for annual leave (equal 1.5 days per month)
if staff join date from March-02-2021 until March-10-2021 will get only 1 day for annual leave (on March month)
if staff join date from March-11-2021 until March-20-2021 will get only 0.5 days for annual leave (on March month)
if staff join date from March-21-2021 until March-31-2021 will get only 0 days for annual leave (on March month)

Note: annual leave: 18 days per year, equal 1.5 days per months

Thanks

[snasui]

Please fill the correct result in target cell for easy to understand.

[Supachok]

what is your value result if range is in
2 Jan - 1 Mar (2021)

[norkaz]

Hi guy

Refer to above some more requirements from both super Excel experts to understand better.

1.Would you please place the possible results for the length of join and the earn annual leave?

2.The earn annual leave is just only for March month,
OR included the other ways by automatically calcuation when the time changes such as April , May ,Jun, … December / 2021?

If yes, please place the possible results for us to understand better.

Norkaz

[Supachok]

You didn't hit to hte point of both requested.
this is hard to solve your question

[sna]

Hi
Here is an attached file

Thanks

[norkaz]

Hi Guy

I'm trying to clarify something that you need but it's still complicated to make it more clear for me about your points.

I mean if my understand is correct your points is not just only for March month -- when the time changes to April, May,... until the end of the year, the results could be automatically updated.

But if you need for all year automatically results , my point is what's the criteria in each month would be -- It's still the same such as 1-10,11-20,21-30 or anything else?

However, I suggest to solve this problems by create the annual leave table like the yellow highlight as attached or you could adapt it to another way like this.

F3
=SUMIFS(INDEX($I$3:$T$8,MATCH(D3,$H$3:$H$8,1),0),$I$2:$T$2,">="&IF(D3<=44197,1,MONTH(D3)),$I$2:$T$2,"<="&MONTH(TODAY()))

Hope this helps.

Norkaz

[sna]

Yes.that's the point but I don't need a helper
is there any way to solve?

[norkaz]

Hi Guy

I don't think without helper table would be better to solve this case.

As the time changes is really important to impact anyting else via the Excel calculation -- then without the helper table ,we need the really complicated formula just only in one cell to calculate the right result and it's quite difficult to find out if anything wrong would happen.

It probably will

For my suggestion the annual leave table would be better to solve the problems like this.

Anyway, without the helper table - please try this.

F3
=IF(D3<=44197,E3*1.5,IF(D3=44256,1.5,IF(AND(D3>=44257,D3<=44265),1,IF(AND(D3>=44266,D3<=44275),0.5,IF(AND(D3>=44276,D3<=44286),0,E1*1.5)))))+IF(D3<=44197,0,IF(TODAY()>44286,MONTH(TODAY())-3,0)*1.5)

Norkaz

[Bo_ry]

Post #6 March-11-2021 I want this earn annual leave show/get only 1 day for claim

F3
=IFERROR(1.5/(1/DATEDIF(MAX(DATE(YEAR(TODAY()),1,1),D3),EOMONTH(TODAY(),0)+1,"m")),--(DAY(D3)>10))


But Post#1 if staff join date from March-11-2021 until March-20-2021 will get only 0.5 days for annual leave (on March month)
G3
=IFERROR(1.5/(1/DATEDIF(MAX(DATE(YEAR(TODAY()),1,1),D3),EOMONTH(TODAY(),0)+1,"m")),0.5*(3-CEILING(DAY(D3),10)/10))

[sna]

Bo_ry could you explain your formula?
Thanks

[Bo_ry]

Go to Ribbon Formula > Evaluate formula