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)

 

[Dave Patton]

What is Day 0 with your calculation?

Excel 2010
	A	B	C	D	E
1	Start	6-Dec-17			6-Dec-17
2	Days	3			7-Dec-17
3		12-Dec-17			9-Dec-17
4					10-Dec-17
5
12a

Cell Formulas
Range	Formula
B3	=WORKDAY.INTL(B1,B2,"0000000",Hol)

Named Ranges
Name	Refers To	Cells
Hol	='12a'!$E$1:$E$4

 

[Marcelo Branco]

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

Why 12/8 is Day 0? Shouldn't it be Day 1?

If you don't want to count the first day maybe...
=WORKDAY.INTL(A2,B2+1,"0000000",Holidays)

M.

 

[saatt79]

Reason for picking 12/8 as 0 Day is because that's our first working day and we will consider any receipt as 0 Day for calculating our Turn around time. Because always the day we receive an inventory is always zero day.
I tried updating the below formulae and I am getting #value error

Why 12/8 is Day 0? Shouldn't it be Day 1?

If you don't want to count the first day maybe...
=WORKDAY.INTL(A2,B2+1,"0000000",Holidays)

M.

 

[saatt79]

Thanks so much. When I apply this formulae I am getting #value error. can you pls help

 

[Marcelo Branco]

Thanks so much. When I apply this formulae I am getting #value error. can you pls help

Worked for me

[TABLE="class: grid"]
<tbody>[TR]
[TD="bgcolor: #DCE6F1"][/TD]
[TD="bgcolor: #DCE6F1"]

A​

[/TD]
[TD="bgcolor: #DCE6F1"]

B​

[/TD]
[TD="bgcolor: #DCE6F1"]

C​

[/TD]
[TD="bgcolor: #DCE6F1"]

D​

[/TD]
[TD="bgcolor: #DCE6F1"]

E​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

1​

[/TD]
[TD]

StartDate​

[/TD]
[TD]

Days​

[/TD]
[TD]

Result​

[/TD]
[TD][/TD]
[TD]

Holidays​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

2​

[/TD]
[TD]

12/06/2017​

[/TD]
[TD]

3​

[/TD]
[TD]

12/13/2017​

[/TD]
[TD][/TD]
[TD]

12/06/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

3​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12/07/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

4​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12/09/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

5​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]

12/10/2017​

[/TD]
[/TR]
[TR]
[TD="bgcolor: #DCE6F1"]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Formula in C2
=WORKDAY.INTL(A2,B2+1,"0000000",Holidays)
where Holidays = E2:E5

Check if the date in A2 is a real date (number), not text.
Try in an empty cell
=ISNUMBER(A2)
it should return TRUE if A2 contains a date.

M.