• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automating Month End Calendar

E

Excel-Access

Member
Dear Excel Community

I work in accounts and what I am trying to do in the attach template is to automate a Month End Calendar.

For accountants they know that generally WD1 is the 1st of each month. So in my example I have manually done this for September 2015 (which is August 2015 month end reporting). WD1 = Tuesday 1st September 2015.

I have shown the following manually:
My calendar would work out WD minus 1 to WD minus 10 (Monday 17th August 2015) to WD+14 (Friday 18th September 2015). it ignores public holiday that is highlighted in Orange and Saturday and Sunday that is not considered a Working Day

What I would like is a clever way to select the month by drop and it will show WD -10 to + 14 with the Day and Date as shown.

Can any of you clever individuals suggest a best way. If it can be shown for one month excluding bank holidays, saturday and Sunday - I can then copy the other months

The goal is that eventually on Tab 1 if I select the relevant month Column A, B and C would be populated automatically.


Many Thanks
 

Attachments

Last edited:
Try this out. I created a table to store all your holidays (feel free to move it somewhere if needed), and then a few formulas in the table to get the dates. Notes that the formulas in col AM are not all the same, so don't copy down. Cell in yellow is unique, all the cells above are a different formula, and the cells below are different.
 

Attachments

Luke M said:
Try this out. I created a table to store all your holidays (feel free to move it somewhere if needed), and then a few formulas in the table to get the dates. Notes that the formulas in col AM are not all the same, so don't copy down. Cell in yellow is unique, all the cells above are a different formula, and the cells below are different.
Click to expand...

Luke M

Thank you so much - genius methodology by you!
 
Luke M

May I please ask a further question of you. In the attachment you will see the tab August 2015

What is the best formula to use and copy down so that from the Calendar Tab it will automatically Column A = Date, Column B = Day and Column C = WD

Also in the Calendar Tab, if I change AK say from -10 to -11, why does the date not update please?

 

Attachments

See attached. Of the 3 columns you mention, one of them has to be the key/lookup value. I chose to use col C with the WD as it it the best unique value. You can see the formulas I used in col A & B.

In the calendar tab, the formulas are not looking at col AK. The formula in AM46 is unique in that it calculates the first day of the month. All the other formulas then use that as a reference points and calculate +1/-1 workdays away.
I've since changed the formulas to look at col AK, but they still use AM46 as the key starting point. Therefore, changing AK46 (the cells in green), will do nothing.
 

Attachments

You must log in or register to reply here.
Back
Top