You can easily extend this template by copying the column into empty cells onto the right of the last column of the model.
Below is a row-by-row explanation of the example excel file:
B3: Calculate the first day of the month using any date:
The EOMONTH function gives the last day of a given month. “-1” means goes one month backwards. This gives the last day of the prior month. The +1 adds one more day to the last day of the prior month, which gives you the first day of the current month.
=EOMONTH($B$1,-1)+1
An alternate approach will give the same result:
=DATE(YEAR(B1),MONTH(B1),1)
This uses the DATE function rebuilds the date by entering the same year, month, and “1” for the date parameter. 1 is the first day of the month.
B4: Calculate the last day of the month using any date:
=EOMONTH($B$3,0)
(You can actually replace $B$3 with any date and the function would still work.)
B5: Calculate the current month as a number from any date:
=MONTH(B3)
B6: Calculate the year as a number from any date:
=YEAR(B3)
B7: Show only the last two digits of the year.
For example, 1995 will show as 95; 2010 will show as 10. To achieve this, first we use the MOD function to find the remainder when the year number is divided by 100. Then use the TEXT function to “zero fill” the number. (Zero fill forces 2001 to show as “01”, instead of just “1”.)
=TEXT(MOD(B6,100),"00")
If you want to show ’95 and ’10, then you can modify the formula:
="'"&TEXT(MOD(B6,100),"00")
B8: Display date in year-month format (yyyy-mm)
We would use the TEXT function to display the date in a text format.
=TEXT(B3,"yyyy-mm")
B9: Calculate the corresponding quarter of using the month
This formula divides the month number by 3 and CEILING always rounds the number up. (Not rounding it to the nearest integer.)
=CEILING(B5/3,1)
B10: Show the quarter and year of a date.
This formula shows the quarter and year in the form of [Quarter]Q[Year]. For example, first quarter of year 2000 will show as 1Q2000.
=CEILING(B5/3,1)&"Q"&B6
B11: Show the quarter and last two digits year of a date.
This would show a date in the short form of [Quarter]Q[Year], where only the last two digits of the year is shown. For example, first quarter of year 2000 will show as 1Q00.
=CEILING(B5/3,1)&"Q"&TEXT(MOD(B6,100),"00")
B12: Calculate the number of days in a month.
The formula minuses the last day of the month to the first day of the month, plus one.
=B4-B3+1
If this was a standalone formula without depending on other cells, the complete formula would be:
=EOMONTH(B$3,0)-EOMONTH(B$3,-1)
where B$3 is the cell with the date you are trying to calculate for the number of days in the month.