Work-date project

[greekboyuk]

Hi,

I am trying to simplify the way the users fill in a spreadsheet and make it easier for them.
I have attached a copy of a dummy wb where I explain what I am trying to achieve.

The Input_data sheet is the first sheet that a user will have to fill in. The only thing that they will update (to start with) will be the year which is located in cell D1 (this might change later so I don't want to restricted to that cell only).

In the same worksheet, I have used a DATE formula to bring all the days of that year. (Please be aware that I am using financial years, so year 2012 will start from 1/4/12 and end with 31/3/12. The only dates that I am looking to bring up with be the working days and the start from Monday and finish on Friday, therefore Saturdays & Sundays are not required in my project. This formula was used to find a way to bring the list into the update workbook, however it would be ideal if the dates weren't listed on the Input_Data worksheet but go directly in the update worksheet

The update worksheet is the sheet were the dates will change according to the financial year. The only changes that I would like to make, will occur in column A, starting from cell A6:A265. I would like column B to stay the same, starting from Monday to Friday all the way through the year.

I know how to get rid of the weekends using a Weekday formula however the difficult part that I struggle with is keeping the Column B the same and identifying the correct dates in column A.


An example:

Let's say that the user changed the Financial Year from 2012 to 2014.If we go to the update sheet, cell B6 is Monday and 1st day of April 2012 is Tuesday. In this case, I would like the Cell A6 to be blank, and Cell A7 to take the date of 1/4/14 and fill the rest of the dates in column A.

Please find attached a copy of the dummy workbook.

Many thanks in advance