Calculating holiday entitlement accumulated to date based on today() and to meet condition

[annazet]

Dear All,

I think I am overthinking this but can someone please have a fresh look at the attached file?


i need to calculate holiday entitlement acquired to date in column V
but conditions apply
If AG<0 then
1. if AF(start date) is greater than 06/04/2015 (cell D2) then need to count weeks between AF(start date) and today()
2. if AF(start date) is earlier than 06/04/2015 (cell D2) then need to count weeks between D2 and today()

but if AG is >0 then
1. if AF(start date) is greater than 06/04/2015 (cell D2) then count the weeks between AF(start date) and AG(end date)
2. if AF(start date) is earlier than 06/04/2015 (cell D2) then count the weeks between D2 and AG(end date)

I have came up with months formula for my monthly holiday calculation but struggle with weekly.
My monthly formula is:
=((X8*5.6)/12)*IF(AND(AE8<$D$2,AF8=""),IF(DAY(NOW())>=DAY($D$2),0,-1)+(YEAR(NOW())-YEAR($D$2)) *12+MONTH(NOW())-MONTH($D$2),0)

would greatly appreciate any help.
Thank you!!!!

[Aardigspook]

Hi,

I think this will work, using WEEKNUM to check the number of the week for Start Date, End Date and D2. I've also replaced your AG<0 with AG="" and I've used TODAY() not NOW() as it's just the date that matters, not the time.
In English, if End Date is blank, this will subtract the week number of the Start Date from the week number of Today if the Start Date is after D2, otherwise it'll subtract the week number of D2 from Today's.
If there is an End Date, it'll subtract the Start Date week number from the End Date week number if the Start Date is after D2, otherwise it'll subtract D2's week number from the End Date's.
Running this on row 8 gives an answer of 20 for today, which looks to be correct to me, having manually counted weeks on a calendar.

Please Login or Register  to view this content.

Hope that works for you.

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.

[annazet]

Thank you so much. Maybe i can solve.
Your method does return current week 20th but doesn't return anything if AG>"" = when someone left

i have changed the formula as follows so far
=((Y8*AD8)/52)*IF(AG8="",IF(AF8>$D$2,WEEKNUM(TODAY())-WEEKNUM(AF8),WEEKNUM(TODAY())-WEEKNUM($D$2)),IF(AF8>$D$2,WEEKNUM(AG8)-WEEKNUM(AF8),WEEKNUM(AG8)-WEEKNUM($D$2)))

sorry it DOES! returns negative value if after 01/01/2016 but i can take it from here.
Need a big nice coffee now!
Thank you.

[Aardigspook]

Glad I could help and thanks for the rep.

Edit: enjoy the coffee

Regards,
Aardigspook

If your problem is solved, please go to 'Thread Tools' above your first post and 'Mark Thread as Solved'.
You don't need to give me rep if I helped, but thanks are appreciated.