Excel Formulae (Date Logic)

[saatt79]

Hi,

I need a formulae to calculate Due Date including Week-end and Excluding Holidays only


Input = Start Date and No. of Days


Output = Due Date


Logic = Start Date+No.of Days-Holidays (include Saturday and Sunday)

 

[Marcelo Branco]

Welcome to Mr Excel forum

Maybe something like this
=NETWORKDAYS.INTL(A2,A2+B2,"0000000",Holidays)

where
A2 = Start Date
B2 = No. of Days
Holidays = named range that contains the holidays

Hope this helps

M.

 

[saatt79]

Welcome to Mr Excel forum

Maybe something like this
=NETWORKDAYS.INTL(A2,A2+B2,"0000000",Holidays)

where
A2 = Start Date
B2 = No. of Days
Holidays = named range that contains the holidays

Hope this helps

M.

I am getting #value error in the cell

 

[Marcelo Branco]

Check if A2 is a real date (number)
Try in an empty cell
=ISNUMBER(A2)
tell us the result

M.

 

[barry houdini]

To get a date as your output I think you need to use WORKDAY.INTL function like this:

=WORKDAY.INTL(A2,B2,"0000000",Holidays)

Where Holidays is a named range including your holiday dates

WORKDAY.INTL is available in Excel 2010 or later versions. For earlier versions you can use an array formula like this:

=SMALL(IF(COUNTIF(Holidays,A2+ROW(INDIRECT("1:"&B2*5)))=0,ROW(INDIRECT("1:"&B2*5))),B2)+A2

confirm with CTRL+SHIFT+ENTER​

_{^{<strike>
</strike>}}​

 

[Marcelo Branco]

saatt79

I misunderstood the question that was very clear: you do want a date as output (do not know where my mind was ..)
Use the formula suggested by Barry Houdini.

M.

 

[Marcelo Branco]

Hi Barry

Do you think this works on previous versions?
=A2+B2+SUMPRODUCT(--(Holidays>=A2),--(Holidays<=A2+B2))

M.

 

[barry houdini]

Do you think this works on previous versions?
=A2+B2+SUMPRODUCT(--(Holidays>=A2),--(Holidays<=A2+B2))

Hello Marcelo,

No, I think the problem with that is that you are assuming the end date (A2+B2) and then adding the number of holidays....but what if there are more holidays between A2+B2 and the new end date?

E.g. A2 = 20/12/2017 and B2 = 5 with holidays on 25/12 and 26/12 - end date should be 27/12

 

[Marcelo Branco]

Ok, i can see now the problem.

M.

 

[saatt79]

Thanks so much for your valuable contribution. I have just checked with one of my scenario to calculate due with your formulae and result seems to be incorrect. Below is the example I used,
Start Date = 12/6/2017 | No. of days = 3 | Holidays = 12/6,12/7,12/9,12/10

Due Date should be = 12/13... Below is the manual calculation for the result. Can you pls help...

12/6 - Holiday
12/7 - Holiday
12/8 - Day 0
12/9 - Holiday
12/10 - Holiday
12/11 - Day 1
12/12 - Day 2
12/13 - Day 3