# of months between two dates

Dear experts,

I need a simple formula which can calculate the number of months between two dates. I have provided a formula which I picked it up from website and I wish to perfect it to:
consider the holiday if the later date is a holiday, then the formula should consider the day after the holiday and then include it as the month

The list of holidays is provided in sheet 'holidays'

Column F has the desired result.


Kindly help.

Thank you
--of-months-between-two-dates.xlsx
ExcellearnerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
Hi,

pls try

=DATEDIF(B9,C9-1,"m")+1

Open in new window

Shouldn't line 5 be 13 Months

Regards
ExcellearnerAuthor Commented:
Rgonzo, yes it should be 13 and not 12.

Can you please help me understand the logic behind -1 inside the brackets and +1 outside the brackets.

Thank you
Rgonzo1971Commented:
we take  may 1, 16 to June 2, 16
that makes
a difference of one month between  may 1, 16 to June 1, 16
plus 1 Equals 2
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Rob HensonFinance AnalystCommented:
I was trying to take a look but having problems downloading the file.

DATEDIF doesn't take into account holidays.

NETWORKDAYS can take weekends and a list of holidays into account but that gives number of working days rather than months.

Thanks
Rob
Rob HensonFinance AnalystCommented:
OK, got the file to open.

Formula in D9:

=IF(ISERROR(MATCH(C9,Holidays!A:A,0)),"",C9+1)

Then couple of options for column F:

=ROUNDUP(YEARFRAC(B9,IF(D9="",C9-1,D9-1),1)*12,0)

=DATEDIF(B9,IF(D9="",C9-1,D9-1),"m")+1

Also some questions:
1) What if Start Date is a holiday?
2) What if Finish Date is a holiday and Finish Date +1  is also a holiday?
ExcellearnerAuthor Commented:
Rob,

Here is the file.
--of-months-between-two-dates.xlsx
Rgonzo1971Commented:
then

pls try
=DATEDIF(B9,D9-(D9-WORKDAY.INTL(D9,-1,MOD(WEEKDAY(D9)+1,7)+1,Holidays!$A$2:$A$20)),"m")+1

Open in new window

--of-months-between-two-datesV1.xlsx

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ExcellearnerAuthor Commented:
Rgonzo,

Can you please explain the logic in the formula. I could not understand it.

Thank you,
Rob HensonFinance AnalystCommented:
I managed to get the file to download eventually before, hence the formulas which I posted.
ExcellearnerAuthor Commented:
Rob,

Also some questions:
 1) What if Start Date is a holiday? Very unlikely
 2) What if Finish Date is a holiday and Finish Date +1  is also a holiday? //Then move the date to the next working day but the Month count should still be in the previous month count or in the 1 month count (the floor value for the result)
ExcellearnerAuthor Commented:
Rob,

The values in column C and D are given I cannot amend those dates
Rgonzo1971Commented:
this part
WORKDAY.INTL(D9,-1,MOD(WEEKDAY(D9)+1,7)+1,Holidays!$A$2:$A$20))

Open in new window

calculates the first non holiday date before D9
this part
MOD(WEEKDAY(D9)+1,7)+1

Open in new window

place the weekend after D9 to not affect the calculation of the non holiday date
ExcellearnerAuthor Commented:
Rob, a correction, sorry for my wrong comment

Values in Col. B and C are given and I cannot change them
Values in Col D are only shown for this illustration to convey my thought process. In the actual file this field would not exist.

Thank you
ExcellearnerAuthor Commented:
Rgonzo,

The number of months should be calculated based on values in col. b and C and column D would not exist in the actual file.

Kindly help.

Thank you
Rgonzo1971Commented:
then my first formula would apply
Rob HensonFinance AnalystCommented:
Am I right in thinking that you would still have to take into consideration the date in column C could be on the holiday list?
Rgonzo1971Commented:
But the author says that the dates are in Col C and they are not adjusted for Holidays
ExcellearnerAuthor Commented:
Dates in col C are given and they can be coinciding with the list of the dates in the tab 'holidays'.

The idea is to get a formula to do the following:
1. check if the value in tab Examples, col C will coincide with the list in tab Holidays, Col A
2. Then the formula should increment the value in tab Examples, col C by one date
3. Further, the formula should check if the value arrived in step 2 above is working day or a weekend (Saturday or Sunday)
4. If the result of step 4 coincides with weekend, then the formula should increment it by 1 (if sunday)or 2 (if Saturday) to arrive at the next working day Monday.
5. if the result of Step 4 is Monday and a holiday (as per list), then it should increment by 1 day again to result in Tuesday.

Now suppose if as per the default calculation col. c less Col. B results in 2 months,
then by following step 2-5 should also result in 2 months and not 3 months.

Sorry if I have confused the forum and I hope I am clear with my request now.

Kindly help
Rgonzo1971Commented:
Hi,

If it does not change the value of the result at the end, why does the formula have to know there is a weekend or a holiday?
Rob HensonFinance AnalystCommented:
I agree with RGonzo, I think you are not comparing like with like, comparing whole months with individual days or small groups of days.

How can single or multiple holiday days have an impact on the result? For lapsed months the only way that holidays will have an impact is when you assign a whole month as a holiday.

Thanks
Rob
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.