Formula to display project month & year number instead of calendar Month & Year in Excel

I want to display dates as project month # and/or year # (i.e., Yr. 1 Mo. 1), rather than as calendar month and year.

I am using a financial modeling template that currently allows me to input the start date in one cell (D4) and it automatically sets all the subsequent dates in the relevant formats, however since the start date is a moving target I'd like to change it to reflect the project month and year number instead of the calendar Month and Year.

• Cell D4 - Displays month & year of start date (input manually)




• Cells E4:BL4 - Display month & year. Sample formula for cell E4:

  
  

      =DATE(YEAR(D4),MONTH(D4)+1,1)
  




• Cells BN4:CG4 - Display quarter # & year. Sample formula for cell BN4:

  
  

      =$CI$4
  




• Cells CI4:CM4 - Display year. Sample formula for cell CI4:

  
  

      =DATE(YEAR(E4),MONTH(1),DAY(1))
  

Answer

The Theory

If you want Excel to do date math with its internal functions, it would probably be simplest to keep the existing formulas as-is and then display other cells that calculate project month and year. You can hide the "raw" Excel dates if you want, or just use a different portion of the sheet or another sheet to display the desired output. Otherwise, every date cell will need a custom-built formula that does manual date calculations, and if the calculations need to consider time at the day level (see below), you may need fancy custom formatting in addition.

Project Dates: Elapsed time is just date subtraction, except project dates typically start with 1 instead of 0 (i.e., a task that happens on the first day is day 1; simple date subtraction would yield zero), so you need to either adjust the "starting" date used for these calculations, or add 1 to the day, month, and year of each calculated project date. The simplest approach is to use an adjusted starting date. Exactly how you adjust it depends on the time convention you use, as described below.

Project Time Convention: Will project months be calendar months or elapsed time? For example, say the project starts on June 29. Is a task on July 1 in month 1 of elapsed project time, or month 2 because June is month 1? The same applies to the year. If you want to base project month on calendar month, you can use Excel's month and year number to do the math.

If you want to use elapsed duration, you would need to calculate from Excel's day number and convert that to months and years. For short projects, you will be close if you just use 30 days as one month. For long projects, you might want to come closer to a calendar year duration by doing something like using the days per month of the associated calendar months to calculate elapsed project months. If your desire is a consistent measure of time when project dates move, you are probably best using 30 days as a project month and just recognizing that each year will be almost a week short.

Implementation

So let's look at how you would implement this.

• Project Month/Year = Calendar Month/Year: If you are going to base project months and years on calendar months and years, I would store a "basis" date equal to one year and one month prior to your project start date. You don't want to change the day in this case because that could put you into the wrong month. So if your actual start date is in cell D4, your calculation date would be:

  
  

  =DATE(YEAR(D4)-1,MONTH(D4)-1, DAY(D4))

  
  

  For illustration purposes, let's say that is in cell Z1. Now all project date calculations are found by subtracting this value from the actual project date. So say you have an event date in cell E4. You would display its project date in an associated cell somewhere as:

  
  

  ="Yr. "&YEAR(E4)-YEAR(Z1)&"  Mo. "&MONTH(E4)-MONTH(Z1)
  

  
  

  An event in the first month would display as Yr. 1 Mo. 1.




• Project Month = 30 days: If you want project time to represent duration independent of the calendar, it will require a little more date math. Dates are stored internally as the number of days since a basis date, so you can work directly from that. In this case, the calculation basis date would be one year and one month prior to your start date, which is 390 days using 30 day months. So the calculation date stored in Z1, in this case, would be:

  
  

  =D4-390

  
  

  (Edit: this originally also adjusted for "Day 1", but since you aren't working at the day level, this adjustment isn't necessary and I removed it.)

  
  

  Again, say your event date is in cell E4. Its project year is: INT((E4-Z1)/360).

  
  

  For project month, we need to ignore whole years, which are already accounted for, so project month is: INT((E4-Z1)/30)-12*INT((E4-Z1)/360). INT is the integer function that truncates to whole numbers. So the displayed project date, using the same formatting as before, would be:

  
  

  ="Yr. "&INT((E4-Z1)/360)&" Mo. "&INT((E4-Z1)/30)-12*INT((E4-Z1)/360)