Choose date and calculate onwards

[werner123]

Morning

I have this table to check the current fiscal month , I use it to calculate employee leave days,it work fine if employee has joined before start of fiscal month , how to I automatically let excel calculate if say employee joins in August , which will be month 1 then and September will be month 2 and so on. Till March which will reset back to 1 , I have a column where I can put the month joined in .




Reference Table












April















January
February
March
April
May
June
July
August
September
October
November
December


Month
1
2
3
4
5
6
7
8
9
10
11
12


Fiscal Mth
11
12
1
2
3
4
5
6
7
8
9
10

















































fiscal month













2





leave days 1,75 per month















=CHOOSE(MONTH(B7);F7;G7;H7;I7;J7;K7;L7;M7;N7;O7;P7;Q7) , gets me the current fiscal month number
I just multiply the fiscal month number with 1,75

[p45cal]

Attach a sample file.

[werner123]

Hope it helps

[werner123]

I was thinking in this line , how do I use the choose function , lets say employee A have a join date of August , and in column H1 it says August .
Am I on the right track ?




March
April
may
june
july
august
september
october
november
december
January
February


March
1
2
3
4
5
6
7
8
9
10
11
12


April

1
2
3
4
5
6
7
8
9
10
11


May


1
2
3
4
5
6
7
8
9
10


June



1
2
3
4
5
6
7
8
9


July




1
2
3
4
5
6
7
8


August





1
2
3
4
5
6
7


September






1
2
3
4
5
6


October







1
2
3
4
5


November








1
2
3
4


December









1
2
3

[p45cal]

This is proving interesting (read: not as easy as I thought!).
You have in cell D23 a header: date joined, but below that are just text strings of months. It would be easier if we could have a full Excel date in there being the actual date they joined, including the year; possible?
I think we can get the result in one relatively short formula (I hope!) without recourse to tables or helper columns, to give you the number of leave days.
What's the actual calculation for leave days (not necessrily a maths formula, describe in words)? (if it's actually based on number of days of employment it could be even easier)
Say the fiscal year begins 1st March, someone joins 2nd March, what's their entitlement on the 3rd March?, what's their entitlement on the 20th March?
Is the fiscal year actually 1st March?

Sorry there's more questions than answers at the moment! (I'm exploring using the functions EDATE, MOD, DATEDIF at the moment)

[werner123]

Thank you for your effort , much appreciated.
Yes date joined can be full excel date.
if someone join on the 2nd of March or the 20th , does not matter , it will be 1,75 days for the month of March( so date can always be 01/MM/YY) , total leave days per year are 21.
Yes the fiscal(tax Year) ends 28 Feb

This is only for leave days , so year not that important , I have columns that deduct leave taken and to carry leftover to next year

And Have a Macro that will reset all dates to March in the Date Joined column for employees of the previous year( that will run on the 1st of March every year , so then all run from March

[p45cal]

Maybe:
=(DATEDIF(MAX($K$3,EDATE(EOMONTH(D22,-1)+1,DATEDIF(D22,TODAY(),"y")*12)),TODAY(),"YM")+1)*$O$1

See attached Excel file.
18939

[werner123]

wow , thank you so much , that is really a beautiful piece of formula , tested little bit and seems to be working 100%

[werner123]

just one thing , if someone joined in previous years , it shows the days still as the month joined date , where it should be running from 1 march , if you cant change it I will run a macro to default the dates to 1 march

[p45cal]

Can you come back with a file where this happens and you highlight the fault?

[werner123]

Thank you

[p45cal]

Test the attached.

=(DATEDIF(MAX($K$3,EDATE(EOMONTH(D22,-1)+1,DATEDIF(DATE(YEAR(D22),MONTH(D22),1),TODAY(),"y")*12)),TODAY(),"YM")+1)*$O$1