Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Calculate # of months per year with a given start date and duration in months

0

I have a spreadsheet with project start dates and duration.  I need to figure out how many months the project will run per fiscal year. I was able to figure out a formula for 2021 start dates, but when the project start was 2022, the formulas was no longer running properly. 

The sample attached is a simplified spreadsheet of what I'm using.  I am looking for a formula to calculate the number of months for 2021, 2022, 2023, 2024.  

i.e. first project is 20 months, starts 2/1/21:  2021 = 10 mos; 2022 = 10 months; 2023 = 0 mos; 2024 = 0 mos.

Any assistance is greatly appreciated.

Mary

Answer
Discuss

Answers

0
Selected Answer

See revision note below.

There may be an easier and more elegant way to do this but this seems to work...

In the attached file, I've added a series of dates in row 1 for what I think are your year end dates (sorry the format is European- I struggle to see things using mm/dd/yy format!). There's also a new hidden column Q (which makes copying formulae easier for me)

In typical "months" cell R5 say, the formula is:

=ROUND(12/360*IF(AND($J5<=R$1, $P5>=P$1),IF(AND($J5>=P$1,$P5<=R$1),$P5-$J5,MIN(R$1,$P5)-MAX(P$1,$J5)),0),0)

The outer ROUND(12/360....,0) bit converts and rounds a number of the days into months (I used 360 since the enddate in column P use EODATE which is on a 360 day basis I believe).

Inside that is IF(AND($J5<=R$1, $P5>=P$1),....,0) which checks the conditions when the project can have days worked in a given financial year (comparing project start and end dates with same for the current finacal year).

Within that is IF(AND($J5>=P$1,$P5<=R$1),$P5-$J5,MIN(R$1,$P5)-MAX(P$1,$J5)) where the AND checks if the project starts and ends in that FY and the true / false statements following that calculate the appropriate number of days.

Hope this helps.

REVISION

Apologies but my solution above added financial year end dates of November 30 (which I thought was very unusual) since the problem referred to the first project having 10 months on FY 2021 and 10 in FY 2021. In the file, the first project is 20 months starting Feb 1 however (which is 11 not 10 full months to the year end and 9 months in the subsequent year)

Also it was midnight local time when I posted that and I hadn't checked the hidden columns. Luckily @Variatus is very thorough and his answer prompted my to check. I saw you have start and end dates hidden in rows 2 and 3 respectively so I could:

1) modify the formula to refer to those instead, so new Q5 (I deleted the column I'd added) reads:

=ROUND(12/360*IF(AND($J5<=R$3, $P5>=R$2),IF(AND($J5>=R$2,$P5<=R$3),$P5-$J5,MIN(R$3,$P5)-MAX(R$2,$J5)),0),0)

2) removed the false dates I'd added in row 1.

In the attached revised file, I've also made the correction suggested by @Variatus for column P dates.

Please check this file (...v0_b.xlsm) is correct, noting that e.g. the first project "Chicago  Project #1" in row 5 now shows 11 months in 2021 and 9 in 2022( not 10 + 10) .

Incidentally I could see no macros in your file but I didn't save it as the safer .xlsx in case you'd removed the macros before posting)

Discuss

Discussion

Thank you John_Ru.  This worked great with the results we need.  Appreciate the help and quick response!

emhedin (rep: 2) Oct 28, '20 at 8:40 pm
Add to Discussion
0

@John_Ru was faster. He posted while I was still working. However, my solution is a little different. Therefore I'll offer it here.

=IF(AND(COUNTA($J5,$O5)=2,$J5<=R$3),IFERROR(DATEDIF(MAX($J5,R$2),MIN($P5,R$3)+1,"m"),""),"")

There is a small logical error in your worksheet which may affect the calculations of the above formula occasionally. I suggest you change the formula in column P to [P5] =EDATE($J5,O5)-1, in effect deducting one day from the reesult. That is because EDATE will calculate from the first day of a month to the first day of another month whereas the work will be done from the frist day of a month until the last day of another month, finishing one day earlier than the function calculates.

This difference is important because your year ends on December 31 (hard data in row 3) and the dates in column P and row 3 must be comparable.

Discuss

Discussion

@Variatus - thanks for 1) giving me a clue to improve my solution and 2) mentioning the DATEDIF function.

I'd never used nor seen DATEDIF in my 2016 version of Excel but have since read it's "kind of a hidden feature in Excel. I guess Microsoft doesn’t want people to use it because the only reason why they created it was to be compatible with Lotus 1–2–3 files".

I see that (despite no function prompts) that DATEDIF works in 2016 and have read that later versions of Excel have added the usual help files but I'm not working now so can't afford to upgrade Excel at present :-(
John_Ru (rep: 6102) Oct 29, '20 at 6:01 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login