Creating attendance tracker

Hi
With the enclosed excel file, we can create attendance sheet 12 months and for limited years. Can anybody help me to create this excel file as any year calendar that too all the months in a single sheet and year as its tab name. Also weekly holiday to be changed as 2nd Sat, 4th Sat and all Sundays.
Advance thanks
Buvanamali

Added a formula offering with VBA to change the Tab name

Please Login or Register  to view this content.

Dear Mr Nankw83

Thank you very much for your kind and quick response. When I ran your macro it created a file horizontally for 365 days and the days above date didn’t change. However, I had shown my requirement in Sheet1, for your ready response. Also please note to shade 2nd Sat, 4th Sat and all Sundays as Week end holidays.
Thank you Sir once again.

Buvanamali

For February you have omitted the Name, ID,PF columns: is this correct?

And the Saturday weekends every other week i.e, 2nd, 4th, 6th... Saturdays

Will the formula solution (modified to your latest post) in post #4 be OK ?

Dear Mr John Topley
Thanks you very much for kind and quick response. I had erroneously omitted to include Name, ID, PF ID etc. in subsequent months. What you have presented in Sheet 2021 is only my expectation. Here it is up to April only. But it should be for the whole year. Besides these, for all the months, the Second Saturday, Fourth Saturday and all Sundays are to be shaded as Week Ends.
But one more correction, in the month Feb, days are not coming between 16th and till end.
My humble request is to have vba solution rather than formula solution because every year we have to make borders manually. Further it will be more user friendly.
Also I bring to your notice that the example file attached in your post #7, when you change the year in the column B1 (drop down list), it doesn’t have any effects in the monthly rows below it.
Thank you Sir once again.
Buvanamali

Dear Mr John Topley
Thanks for your immediate response. I verified your attachment with the post #9 which addresses the dropdown years also. With regard to my another requirement, you had marked 2nd Sat and 4th Sat in the months Jan, Feb and in March only 2nd Sat as RED not 4th Sat. I request you to mark all the 2nd and 4th Sat as holiday besides Sunday.
You may extend this rule upto Dec for any year.han
Thanks in advance
Buvanamali

The point of the post was just to confirm that the LOGIC was correct.

As the months are separate "blocks" of data, the logic is a little more complex than having a single (year) row. Using formulae or VBA, you need separate CFs for each range (block).

Dear JohnTopley

I confirm your logic and idea is correct. You can go ahead further. I need only result whatever the mode be.
Buvanamali

Originally Posted by AliGW

OT - are you still using Excel 2013? If not, please update your forum profile.

Mam
Still I am using 2013 only

Dear Mr KvSrinivasamurthy

Thanks for your response. Your file created all the 12 months at a stretch. But my intention is to create the months attendance at a gap of 20 rows between two months in the same sheet shall be appended below. For your immediate reference, you may refer to the attachment in the post #9. However I thank you once again for your kind response and solution.

Thanks Sir once again.

Buvanamali

Dear Mr KS Srinivasamurthy

Really very nice job. Thank you once again. But I noticed some discrepancies. The Calendar from April to October stopped with Date 28, for Nov it shows 31 days and Dec with 28 days. But for the above errors, all are Ok. If the above one is rectified everything is OK.

Thank you once again.

Buvanamali

Dear Mr KS Srinivasamurthy

Thanks Sir. It works Fine. Once again thanks.
Have a nice day.

Buvanamali

Dear Mr John Topley

Thank you Sir for support and for having spared your precious time on my request. Thank you once again.

I found some errors in CF with regard to Second Saturday and Fourth Saturday. I am enclosing a separate excel sheet showing the errors for the years 2021 – 2025.
Except these, all other things are working fine.

Advance thanks

Buvanamali

Errors occur because my assumption on 2/4 was that there was a continuum over the year rather than within individuals months so you could simply apply KVS's CF formula,

CF amended

Dear M/s John Topley and KVS

Thank you very much to both of you for your excellant contribution. I have marked the thread as solved.

Once again thanks

Buvanamali