Leave planner - Auto adding data in future dates.

[dead*]

Hi,
I am trying to change the existing leave planner so that excel auto adds leave entitlement based on the actual real time date. The leave planner is a 2 year spreadsheet to allow a full 12 months to each member of staff as they have different start months for their leave allocation. I have tried, without success, to use the today() function as a point of reference for a formula but excel just keeps laughing at me
hoping someone can help with this as, at the moment, staff are always in advance or arrears with their leave entitlement and most of the records have to be kept and made on good old paper.

[Squeaky]

Hi dead*,
There are some parts to this I do not understand fully. Under STATIC ENTITLEMENT ... NEVER CHANGES D & J. you state the amounts never change, but in the bold description you say
column J needs to auto populate when column "C"... What column did you mean?

NOTES and IDEAS: For the holiday start months you do not have actual dates, you have text words. Using Bill as an example, enter in 3/1/2020, then on the home tab, number section, select More Number Formats... in the
drop down box, then select custom. Under the word TYPE, in the box place mmmm and hit OK. It should display the date as March. Do that with each holiday start. Format painter will work great here.
You can now use that date as a fixed reference in your formulas. You can use greater than "<" or less than ">" with the today() formula.
SO the formula =if(eomonth(today(),-1)+1>=date(year(c8)+1,month(c8),Day(c8)),f8+j8,"")
will basically take the start date, add 1 year to it, then compare today's date to that. If it is equal to today or later, it will compute by adding the amounts in F and J. If not, the result is "".
You can place it where you need it to compute, and change the cell columns you want to add. Just make sure the Holiday Start Month "YEAR" is 2020, not a persons actual start year.
Explanation notes...
eomonth(today(),-1)+1 computes the end of this month. The -1 makes it the end of last month, and the +1 adds one day to make it the start of this month.)

Let me know if this helps.

[dead*]

Hi Squeaky,
Thank you for your response, excel seems to make it rather complicated to 'automatically add a value' in 12 months from a given date.
My apologies for the confusion regarding the 'static' element - what i was trying to say is that the anual leave entitlement (basic number of days ) will not change in the future and that only adjustments from the previous year will affect the number of days. ie. leave days not used or days taken in advance.
I have adjusted the date part to match UK dates so " Using Bill as an example, enter in 3/1/2020" becomes " Using Bill as an example, enter in 1/3/2020".
For the purpose of clarity could you explain " Just make sure the Holiday Start Month "YEAR" is 2020, not a persons actual start year."?
I shall try the formula early next week on a test setup to see if i can get it to work - would this be possible if i set the 'date' as a date in the past so that the formula is live?
Thanks again for your help, genuinely appreciated!

Dead*

[Squeaky]

Hi Dead*,
What I mean is when you put in the start date, use the current year, not the year someone actually started. Since the formula adds one year, if say someone started in 2015 and you put that as his start date, it will compute to 2016 which will not work right on your form. Sorry for being confusing.
I am confused as to what "past" date you are referring to. Try working it and post if you need more assistance.