Date Formula: Calculate the number of days in a specific month between two dates

Nick Wilson

I'm working on a program tracker that includes revenue forecasting in each month. Project durations can range from one week to six months or more and I want the revenue to be forecasted evenly over the duration of the project.

A date formula that calculates the number of days in a specific month between two dates would be amazing! I've highlighted a simplistic version of what I'm trying to accomplish with the formula in green.

spauliszyn

@Nick Wilson it sounds like you want to proportion a "something" (in this case $10k) over certain months. This can be done through the basic functions although a bit convoluted.

This is my result and matches your sample snippet:

• This is the text copy of the "January" formula to copy: =MAX(0, NETWORKDAYS(MAX([Start Date]@row, DATE(2024, 1, 1)), MIN([End Date]@row, DATE(2024, 2, 1) - 1))) / NETWORKDAYS([Start Date]@row, [End Date]@row) * [Project Revenues]@row
  • There are no other hidden 'helper' columns. All formulas are "column formulas" so you only need to edit the one formula for each.
  • The MAX/MIN functions are there to automatically reject column 'months' that are not within the start and end date ranges.
  • You just need to edit the year/month numbers in the two DATE() functions to match your column month. You could put in the exact date of the last day of the month in the second DATE() function, but I find it easier and less exposure to error if I just chose the first day of the following month, then subtract a day. i.e. "DATE(year#, next_month#, 1) - 1"

    
    

Nick Wilson

Well gosh @spauliszyn, you don't need to make it look so easy. lol

In all seriousness, thank you so much!!! That works perfectly.

Allison Bishop

Very Helpful!