Automatically populate a 12 mth calendar with result depending on start and finish dates

Hi ,

I've just started working on an FTE calculator and wish to populate a 12 month calendar with FTE depending on the start and finshed dates.

FTE Calculator non nursing.xlsx

I've attached the file to help explain. In Column D the user would select the month the staff start and in the Column E the month the staff will finish. I would like the fte that is calculated in Column Z then to populate in the 12 columns AB:AM (Jul to Jun) with corresponding month start and finish.

Is this possible?

thanks

If I've understood your requirement correctly this is relatively easy to achieve but needs some specific inputs and custom formatting, see attached
I've set up a custom date format to display just the month in MMM style and applied this to columns D and E and the range AB7:AM7. I've also set up data validation on columns D and E to named range "year" on Sheet1 range $A$54:$A$65 which contains the first day of each month from July to June. The user simply picks the 1st day of the start month in column D and the 1st day of the end month in column E. The 1st day of each month is entered in the range AB7:AM7. The FTE for each month between the start and end dates is then given by the following formula

Please Login or Register  to view this content.

in each cell in the range AB8:AM40.

I've also added some simple code to set up a new year on the Set New Year button.

Regards
Ian

Thats it ! It's exactly what I wanted.

Thank you