Calculating Number of Months Before/After a Specific Date Between Two Dates

Copper Contributor

Operating System: Windows 10 Version 20H2, 64-bit operating system

Excel Product: Microsoft Office Professional Plus 2016

 

I hope everyone is having a good day and thanks in advance for all those who are willing to offer their input.

 

I am trying to calculate the total months before and after a certain date within a certain period of performance. The dates for the Period of Performance are directly linked to the "Budget" tab and will vary from project to project. For our organization, faculty receive salary increases effective July 1 of each year and staff receive salary increases as of 1/1 of each year. 

 

For purposes of the spreadsheet, I am looking to calculate the total months before and after July 1st and January 1st for each budget period. I will then incorporate this information into the "Request" calculations in columns K, M, O, Q, S to calculate a weighted average salary cost for each individual who is budgeted on the project. 

 

For example, with the current sample budget period, I want cell G5 to output 5 and cell H5 to output 7. For cell I5, I want it to output 11 and for cell J5, I want it to output 1. 

 

I am open to changing any cells if needed. This format was only meant to help me visualize what I was trying to figure out. 

 

Thanks!

3 Replies

@aagoldman Attached a possible solution for you. not very straight-forward and certainly not very pretty. But, I think it works as intended. I've introduced a few names ranges to keep the formulae easier to follow. 

@Riny_van_Eekelen Thank you! This seems functional and produces the results I was looking for. Now, I will try to examine the formula and try to understand it so I can explain it to my colleagues. I really appreciate your assistance. 

@aagoldman Glad it worked. And when you examine the formula, break it down in smaller pieces and see what every part is "doing". Not very straight-forward, I must admit. Come back here if you need more help.