Creating a Dynamic Calendar Table

[David Evans]

Anyone created a dynamic Calendar which is 12 months less than today's date and 24 months hence?

I'm trying to come up with a technique to assign numbers to Workdays in each month, and I think that's where I need to start the quest ..

If anyone knows better, please guide me!

 

[NARAYANK991]

Hi ,

I am somewhat confused by the problem ; what is the connection between a calendar and assigning numbers to workdays ?

What is the kind of calendar template you had in mind ? Is it to be developed using worksheet cells or a userform or ?

There are a number of Excel calendar templates available on the Internet ; what exactly is the problem with all of these ?

Narayan

 

[David Evans]

Sorry Narayan - I was a bit vague in my question .....

Here's a bit more info - I want to create a dynamic or perpetual calendar, where the Bus Day in Col F is incremented once for each Workday in a month, and when a new month comes along, starts again, etc.

We'll handle Holidays later, unless you can solve it all in one fell swoop.

Thanks for thinking of me, and, for me

David

 

[Shaun]

Hi David,

I am sure there is a better solution available, but the attached works.

Cheers,

Shaun

 

[NARAYANK991]

Hi ,

The following formula should give the working day for any date :

=SUMPRODUCT(--(WEEKDAY(ROW(INDEX($A:$A, A1):INDEX($A:$A,A1 - DAY(A1) + 1)),2) = {1,2,3,4,5}))

A1 has a valid date.

Narayan

 

[David Evans]

As Usual, @NARAYANK991 hits it for a 6!

Ok - my next slow bowl to you is as follows - I want to include the holidays for 2016 in my Workday Check in Col E ....For example, the 4th of July is a holiday, therefore it should not be a workday ..... Currently my formula is looking for only Day 6 or 7 - can you write something that will include the date range on the second sheet for 2016?

 

[Atlas Johnson]

You can easily do it with NETWORKDAYS.INTL function which also takes Holidays as a parameter.

To check if DAY1 is a work day:
Use: NETWORKDAYS.INTL(DAY1,DAY1, 1, HOLIDAY_LIST)
Where 1 stands for weekend type Saturday and Sunday
AND HolidayList is a range full of holiday dates.

Cheers,
AJ

 

[NARAYANK991]

Hi David ,

See the uploaded file.

Narayan

 

[David Evans]

I will be back on this little teaser this weekend with a bit of luck - thanks to all of you for your ideas and contributions.