# 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.
Thanks,
Nicolene
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ConsultantCommented:
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
Finance AnalystCommented:
See attached with options for change of Holiday year start date and total entitlement.
Holiday-Calculator.xlsx
0
Commented:
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
Finance 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

Experts Exchange Solution brought to you by