Excel formula needed

Hi there
I need a formula that calculates how much leave a staff member has at the current date (month), according to their start date, earning 1.75 days (example) per year.
Please can someone help?
Thanks,
Nicolene
Nicolene van der VeeAsked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
To calculate accurately you need to take into consideration a number of pieces of information and scenarios:

Information:
1) Holiday year range - not necessarily January to December, our holiday year is April to March
2) Total entitlement for a whole year

Scenarios:
1) Person was employed before Holiday year start date and still employed after Holiday year finish date - full entitlement.
2) Person was employed before Holiday year start date and left employment before Holiday year finish date - pro rata entitlement based on Holiday year start date through to Employment finish date.
3) Person started after Holiday Year start date and still employed after Holiday year finish date - pro rata entitlement based on Employment Start Date through to Holiday year finish date.
4) Person started after Holiday Year start date and left employment before Holiday year finish date - pro rata based on number of days employed.

The file I uploaded covers all of these issues with calculations for total entitlement, pro rata calculation if necessary, and for entitlement to date.
0
 
AlanConsultantCommented:
Hi Nicolene,

If you have a start date in cell A1, then this might work for you:

=(YEAR(TODAY())*12+MONTH(TODAY())-(YEAR(B1)*12)-MONTH(B1))*(1.75/12)

So, if you have 1 Jan 2010 in A1, then you would get:

14.29 (days)

You might want to take just the integer part of that to get 14 (days):

=INT(=(YEAR(TODAY())*12+MONTH(TODAY())-(YEAR(B1)*12)-MONTH(B1))*(1.75/12))


Does that work for you?

Alan.
0
 
Rob HensonFinance AnalystCommented:
See attached with options for change of Holiday year start date and total entitlement.
Holiday-Calculator.xlsx
0
 
Douglas StacyCommented:
You can use the following formula to do the needful task:
(YEAR(TODAY())*12+MONTH(TODAY())-(YEAR(B1)*12)-MONTH(B1))*(1.75/12)
0
 
AlanConsultantCommented:
Good advice offered.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.