Formula for recognising dates between and outside a range of start and end times?

Thanks for help with this problem:

I am building a staff salary forecast for the next 5 years, annually.

I have a Start Date, an End Date, and then the Year End dates for each of the 5 years I am forecasting. I also have the Annual Salary.

I need a formula that helps me a) calculate Part salary in a year where an employee joins during the year. I have figured that out using the DATEDIF formula. So I can calculate the amount of time for the year a person works.

What I am not able to figure out, is what formula do I add to say "if the person is working during this financial year THEN insert their salary for the year". Meaning, what if a person leaves (End Date) half way through a year OR a person works full time for the 5 year period?

Also in my table, I do not have End Dates for employees at present because no one has left yet but I want to build it that if/when someone leaves, I can calculate their Part Salary for that year in which they leave. So the only inputs I have are a Start Date and then my 5 financial years and their salary amount?

Can anyone please help me figure out these situations (to account for someone leaving in next 5 years so only working a part year) AND to recognise a normal full years salary in the full years between the start and end year?

You can have a logic of it startdate <= last day of year year which is the same as < first day of next year (so they have worked in the year), and Enddate > start of financial year or blank then do the calculation

enddate, end of financial year

datedif(min of (end date , end of year),max(startdate, star of year),"D")/365

but is this how your staff are paid? most staff are paid monthly so the payment is parts of the partial months and a 12th of the salary for ach full month, can you advise?


https://www.mrexcel.com/board/thread...l-137.1153601/

Hi there, thanks so much for your assitance with this. So I am working on the Monthly Salary Forecasts and I do not know how to work the formula to work when I do not know a staff END DATE eg it is Blank for now.

See attached the formula I am using for Start Date and End Date estimates of which months there will be a salary: =IF((AND(G$6>=$D9, G$6<=$E9)),$F9/12,0). where G6 = Current month, D9 - Start Date, E9 = End Date. But this doesnt work when there is BLANK in the End Date?

Try this . If E9 is blank It will take Today's date.

=IF((AND(G$6>=$D9, G$6<=IF($E9="",TODAY(),E$9))),$F9/12,0)

Thanks for feed Back .Pl mark the thread solved.