Formula which can calculate how many days in a month and create a column for each day

Formula which can calculate how many days in a month and create a column for each day. Columns for day 28, 29, 30 or 31 also need the formulas in columns 1 to 27 copied over automatically. [sheet name “LARSheet1 to 12” ]

I’m trying to build a “Leave, Attendance Management” system using Excel”. I am about 40% there so far.

I have a few problems to tackle which I will post in separate treads and link them back to together; so others can hopefully learn from the help I receive (and my mistakes):

The basic structure of the workbook is as follows:

12x main monthly ‘Leave Attendance Record’ worksheets (i.emonths April 2012 through to March 2013). [I will refer to these sheets asLARSheet1 to LARSheet12 ].
1 x worksheet which holds ‘data validation’ list andvlook up info. [“FormulaListSheet”]
1 x worksheet acting as a database of staff details andpay numbers. [“StaffdBaseSheet”]
1 x summary page which shows staff reaming leaveallocation. [“SummaryLeaveSheet”]

What I’m having problems with at the moment is as follows:


A formula or a macro which works out how many days are present in a given month [starting date of month located in CELL B10].
Days 1-27 will be already set up on the sheet. Based upon the monthly value I would like a solution which can calculate how many days are in a month and create a column for days 28, 29, 30 or 31 (depending on the month).

Hopefully I would like it to work like this:

When the user opens a new worksheet, they first entre the month start date in CELL B10. Hopefully this will trigger columns E13:AH22 to update with the relevant week day & date.


I also need the formulas and formatting in cells AH13:AH100 (column for day 27) to copied over automatically.


Can anyone help? Cheers moshjosh!


Sample of sheet setup:

screenshot_LARSheet1.PNG

===== ======== ====== ======== ====== ============= ========== ============ ============ ===== ========= =======

This is a multi-part project which is collectively known as “Leave, Attendance Management” system using Excel”. . Other parts are found on other treads. I hope to link the thread solutions together so others who wish to do a similar task can hopefully learn from help I receive (and my mistakes).

The other parts of the system are:

‘Leave attendance record’ (this thread) worksheets (i.e months April 2012 through to March 2013). [I will refer to these sheets as "LARSheet1" to "LARSheet12"].

‘Data validation’ worksheet which holds list and vlook-up info for formulas. [I will refer to these sheet “FormulaListSheet” ].

‘Database’ worksheet acting as a of staff details database. [ “StaffdBaseSheet”].

‘Summary reaming leave’ page which shows a summary of staff used leave and calculates reaming leave allocation. [ “SummaryLeaveSheet” ] http://www.excelforum.com/excel-gene...worksheet.html