Solved

# of months between two dates

Posted on 2016-08-02
20
66 Views
Last Modified: 2016-08-22
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
0
Comment
Question by:Excellearner
  • 8
  • 7
  • 5
20 Comments
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41739071
Hi,

pls try

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

Open in new window

Shouldn't line 5 be 13 Months

Regards
0
 

Author Comment

by:Excellearner
ID: 41739089
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
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41739100
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
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41739269
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
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 250 total points
ID: 41739285
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?
0
 

Author Comment

by:Excellearner
ID: 41739764
Rob,

Here is the file.
--of-months-between-two-dates.xlsx
0
 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 250 total points
ID: 41740162
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
0
 

Author Comment

by:Excellearner
ID: 41740244
Rgonzo,

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

Thank you,
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41740263
I managed to get the file to download eventually before, hence the formulas which I posted.
0
 

Author Comment

by:Excellearner
ID: 41740333
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)
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:Excellearner
ID: 41740335
Rob,

The values in column C and D are given I cannot amend those dates
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41740359
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
0
 

Author Comment

by:Excellearner
ID: 41740438
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
0
 

Author Comment

by:Excellearner
ID: 41740440
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
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41740442
then my first formula would apply
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41740445
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?
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41740458
But the author says that the dates are in Col C and they are not adjusted for Holidays
0
 

Author Comment

by:Excellearner
ID: 41740612
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
0
 
LVL 49

Expert Comment

by:Rgonzo1971
ID: 41740624
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?
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41742264
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
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now