Solved

lapsed months considering start, and end dates coinciding with holidays

Posted on 2016-08-04
13
45 Views
Last Modified: 2016-08-24
Dear experts,

I need an excel formulae which will calculate the # of months between two dates considering the holidays and weekends coinciding the end date of the date range.
Col. I in sheet ‘examples’ has the logic for the calculations to be performed by the excel formula.
Col. E is the internal calculation performed by the excel formula.
Col. F has the desired results expected from the excel formula.

Kindly help.

Thank you
lapsed-months-considering-start--an.xlsx
0
Comment
Question by:Excellearner
  • 5
  • 4
  • 3
  • +1
13 Comments
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41742255
How is this different to your previous question:

https://www.experts-exchange.com/questions/28960993/of-months-between-two-dates.html

EE Moderators do not allow duplicate questions.

Thanks
Rob
0
 

Author Comment

by:Excellearner
ID: 41742279
Rob,

thank you.

In my previous question I was not clear with my thought process and hence I had to make increments/adjustments to my original question.

In the end I do think I was helpful in getting the excel formulae.

I am happy to grade experts based on the insights provided but I do not which formula addresses my requirements.

In this question I am clear with my objective and I hope I have explained the purpose clearly.

I am happy to delete this question provided the experts can now provide their final formula which will address my question.

I am sorry if I come across in my above writing as being harsh.

Thank you
0
 
LVL 18

Assisted Solution

by:xtermie
xtermie earned 125 total points (awarded by participants)
ID: 41742342
=NETWORKDAYS(B7;C7;Holidays!$A$2:$A$5) will yield days

For a month calculation
=ROUNDUP(NETWORKDAYS(B7;C7;Holidays!$A$2:$A$5)/22;0)
given that internationally 22 working days per week, on average
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 49

Accepted Solution

by:
Rgonzo1971 earned 250 total points (awarded by participants)
ID: 41742368
Hi,

I repeat my proposal
=DATEDIF(B4,C4-1,"m")+1

Open in new window

Regards
lapsed-months-considering-startV1.xlsx
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 41742386
I repeat my question - how does a single holiday date impact on the number of months when you want whole or part months to be included?

To give an analogy, imagine you have 5 tankers of oil, if you remove a barrel full from one tanker you still have 5 tankers.

Unless a whole month is assigned as holiday, single days or groups of days will have no impact.

To that regard, I agree with the DATEDIF formula proposed above.

Thanks
Rob H
0
 

Author Comment

by:Excellearner
ID: 41742473
xtermie,

Your below formula:
For a month calculation
=ROUNDUP(NETWORKDAYS(B7;C7;Holidays!$A$2:$A$5)/22;0)


When I put this formulae in cell H4 in sheet examples, the formula did not work and cursor was highlighting on B7
0
 

Author Comment

by:Excellearner
ID: 41742476
Rgonzo1971,

It has given me the desired result.

I need time till EOD today to just understand where I am confused and confusing you all.

thank you
0
 
LVL 32

Assisted Solution

by:Rob Henson
Rob Henson earned 125 total points (awarded by participants)
ID: 41742645
@excellearner - I suspect that the formula from xtermie should go in H7 as it is referring to dates in B7 and C7.

Also the formula has the semi-colon as the argument separator, change those to commas:

=ROUNDUP(NETWORKDAYS(B7,C7,Holidays!$A$2:$A$5)/22,0)

Thanks
Rob
1
 
LVL 18

Expert Comment

by:xtermie
ID: 41742892
Rob is right, try that and it will work...Thanks Rob
due to my regional settings there are ; instead of , in Excel functions
0
 

Author Comment

by:Excellearner
ID: 41743806
Dear xtermie

1      01-May-16      01-Jun-16
The results for the above sample is 2 months

3      02-Jul-16       02-Aug-16
The results for the above sample is 1 month

I do not think this is consistent (or visually correct)

Kindly help.

Thank you
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41744004
Yes you are right, see the attached example, with the formula.
However, as Holidays are extracted (which includes weekends and the holidays you specified) you can see in the example that NETWORKING days are:

01-May-16      01-Jun-16                        23
02-Jul-16         02-Aug-16                       22

So using 22 as the set cutpoint for working days that comprise a month will yield 2 months in the first case and 1 month in the second month

You need to decide what your "MONTH" will be.
For example, what will happen if you have lets say 5 holidays in a month, like in December or no holidays?  If you just want to use the physical difference of the dates as the MONTHS for your calculation but use the NETWORKING days for calculating the days and hours available for work, these are two different things.  In order to calculate months using the NETWORKING days, you need to assume that a MONTH is XX working days and use that number (XX) consistently.  That is my opinion :)

A different approach would be to obtain NETWORKING days for each month, per year, and have its average that as a reference for the XX (which I have done in the example and is....22 , 21.5 actually but rounded...it's 22 :) )

If you want to calculate differently please provide us with more information.
lapsed-months-considering-start--an.xlsx
0
 

Author Comment

by:Excellearner
ID: 41744052
xtermie,

Thank you. You have explained it very well.

I will shortly send you how treasury functions (banks/interbank markets/settlement systems) consider a month.

Thank you
0
 
LVL 18

Expert Comment

by:xtermie
ID: 41768442
Author never came back and all were valid answers
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel 2016 program to loop through scripts to apply filename 2 21
Excel Question 17 15
If help 9 46
Why do my Excel files become huge? 27 31
Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
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…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

776 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