Automatically generate dates for timesheet based on sheet name in Excel

Simon needs to create a timesheet for his staff in excel with one sheet per month. He is looking for an automatic way that will populate the dates for the month in each sheet, such that it has the right number of days irrespective of the month or the year.This can be easily done in Excel by using a combination of a few simple formulas.

I want to create a Excel workbook called Calendar.xlsx with the tabs as January, February etc. Each tab should display the right dates depending on the month in the sheet name. (You can download  the workbook with the actual functions)

 

1) The first part to do that is to reference the sheet name in the sheet itself so we can use it in other formula.

To include the sheet name in the sheet, you can make use of the cell formula. The cell formula is used to return information about the cell.
If you use the formula as
=CELL(“filename”,A1)

It will return the actual excel file name with its path in square brackets followed by sheetname  i.e. [C:\Calendar.xlsx]January

Note: The excel sheet needs to be saved before you use the formula otherwise it will return a blank result

2) then To extract the sheet name from the result above,first  find the position of the close bracket (]) and select the text to the right of it.

Use the find function to find the position of [ in the result

FIND(“]“,CELL(“filename”,A1))

3) Then use the mid function to return the text after the bracket. i.e. January

=MID(CELL(“filename”,A1),FIND(“]”,CELL(“filename”,A1))+1,200)

Note that I am using nested functions to pass the result of one function into the next one. (The previous function is displayed in italics)

 

4) Now that I have the name of the month, I can get the first day of the month using the datevalue function.

Datevalue function can be used to convert text to date. If I pass it a value as “January 1″, it will return  the date 1/1/2014 (Assuming the current year is 2014)

If the result of the  formula in step 3 was in cell B5, I can add the text ” 1″ (space followed by number 1) to it and use the datevalue function to get the first day of the month.

=datevalue(B5 & ” 1″)

5) To get the last day of the month, we simply pass that value to the EOMonth function

=EOMONTH(datevalue(B5 & ” 1″),0)

 

6) The last part is to create a list of dates for the month so that it starts on the 1st and automatically ends on the last day of the month.

Let’s say we want to create the list of dates in column E. We will assume that the start and end date from step 4 and 5 are in the cells B6 and B7

For the first cell which represents the 1st of the month, you can directly reference the cell that we calculated above with the first of the month.

E1 will have the formula

=B6

7) For the subsequent cells lower down in the column we check if the value in cell above is less than the last date of the month. If it is, then we add one to it otherwise we set it as a blank.

E2 will have the formula

=if (E1<B7,E1+1,””)

8) Copy this formula down for atleast 30 rows. That will automatically populate the dates till the last day of the month and the remaining cells will be blank.

 

9) To create the same structure for February, copy the January sheet to a new sheet and change the sheet name to Feb or February. It will automatically create a list from the 1 to the 28 or 29 depending on if it is a leap year or not

10) Repeat the process for the other months.

You can access the workbook with the actual functions here.

Leave a Reply

Your email address will not be published. Required fields are marked *