How to calculate start & end dates, quarters, headings and days in period in a monthly Excel model?

Click here to download the Monthly Model Template with all the necessary start & end dates, month, quarter, and “days in period” calculations. This model is the perfect template to help you set up a monthly model for financial or operational data.
Screenshot of the monthly model template

Screenshot of the monthly model template

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.

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>