Issues with WORKDAY formula not adjusting for holidays...

[fustilugs3]

I’m new to this forum, and teaching myself Excel over time, so please forgive me if I’m missing something obvious. I am having trouble with a formula identifying and skipping over holidays. It’s just a piece in a very large template I am creating, but it is crucial that Excel accounts for the holidays. I have the template in Excel 2007 which I can send, but I didn't see an option to attach to the thread. Here is the formula I’m having issues with when calculating the Due Date:
</SPAN>
=IF(B2=TRUE,IF(HOUR(D2)<17,WORKDAY(C2,1,Holidays!A4:A13),IF(HOUR(D2)>=17,WORKDAY(C2,2,Holidays!A4:A13))),IF(HOUR(D2)<17,WORKDAY(C2,5,Holidays!A4:A13),IF(HOUR(D2)>=17,WORKDAY(C2,6,Holidays!A4:A13))))
</SPAN>
B2=TRUE/FALSE (formula result)</SPAN>
C2=Approval Date</SPAN>
D2=Approval Time</SPAN>

The Due Date is based off several factors, which is why the formula is so convoluted. What I’m going for is this:</SPAN>

-If B2 equals TRUE, and the approval time was before 5:00PM, then the Due Date is the approval date plus one day (skipping over weekends and federal bank holidays.)
</SPAN>
-If B2 equals TRUE, and the approval time was at or after 5:00PM, then the Due Date is the approval date plus two days (skipping over weekends and federal bank holidays.)</SPAN>

-If B2 equals FALSE, and the approval time was before 5:00PM, then the Due Date is the approval date plus five days (skipping over weekends and federal bank holidays.)</SPAN>

-If B2 equals FALSE, and the approval time was at or after 5:00PM, then the Due Date is the approval date plus six days (skipping over weekends and federal bank holidays.)</SPAN>


Every portion of the formula seems to be working (no error values and results are correct), except for skipping over holidays. Any ideas on how I can fix this? Thank you for any advice at all!!


EDIT: Should probably mention that all holidays are listed on a separate tab in "=DATE(YYYY,MM,DD)" format.</SPAN>

 

[Jonmo1]

Welcome to the board.

The most likely cause is the dates you list in Holidays!A4:A13 are not really dates, but just text strings that look like dates.

What does this return
=COUNT(Holidays!A4:A13)

 

[fustilugs3]

10.

 

[Jonmo1]

Can you post a few examples of values in B2 C2 D2?
Along with your expected results (and why you expect that result)
And what the actual (incorrect) results were?
Also include the list of dates in the Holidays Range


Perhaps the years are different between the dates in the Holidays list, and C2 D2

 

[fustilugs3]

jonmo1- Here is some more information. Hope this helps:


B2=TRUE C2=02/15/2013 D2=2:00 PM
B2=TRUE C2=02/16/2013 D2=4:00PM
B2=FALSE C2=02/11/2013 D2=3:00PM
B2=FALSE C2=02/11/2013 D2=4:00PM

The Due Date should return as 02/19/2013, but the formula returns 02/18, which is a holiday on the list below.


[TABLE="width: 300"]
<TBODY>[TR]
[TD]Holidays:</SPAN>[/TD]
[/TR]
[TR]
[TD]=DATE(2013,1,1)</SPAN>[/TD]
[/TR]
[TR]
[TD]=DATE(2013,1,21)</SPAN>[/TD]
[/TR]
[TR]
[TD]=DATE(2013,2,18)</SPAN>[/TD]
[/TR]
[TR]
[TD]=DATE(2013,5,27)</SPAN>[/TD]
[/TR]
[TR]
[TD]=DATE(2013,7,4)</SPAN>[/TD]
[/TR]
[TR]
[TD]=DATE(2013,9,2)</SPAN>[/TD]
[/TR]
[TR]
[TD]=DATE(2013,10,14)</SPAN>[/TD]
[/TR]
[TR]
[TD]=DATE(2013,11,11)</SPAN>[/TD]
[/TR]
[TR]
[TD]=DATE(2013,11,28)</SPAN>[/TD]
[/TR]
[TR]
[TD]=DATE(2013,12,25)</SPAN>[/TD]
[/TR]
</TBODY><COLGROUP><COL></COLGROUP>[/TABLE]

Not sure if this matters, but:
B2 = value determined by IF formula
C2 = value determined by DATEVALUE formula
D2 = value determined by TIME formula

 

[Jonmo1]

I don't know, it works for me..

In my below example the only thing I changed in your formula is to add the absolute references to the Holiday Range, to prevent it from incrimenting as the formula filled down.
A4:A13 vs $A$4:$A$13

If you're filling this formula down, you may want to verify your formula(s) are actually referring to the correct range on the Holidays List.
And apply the absolute references as I did..

Excel Workbook
	B	C	D	E	F
2	TRUE	2/15/2013	2:00 PM		2/19/2013
3	TRUE	2/16/2013	4:00 PM		2/19/2013
4	FALSE	2/11/2013	3:00 PM		2/19/2013
5	FALSE	2/11/2013	4:00 PM		2/19/2013
Sheet1

Excel Workbook
	A
4	1/1/2013
5	1/21/2013
6	2/18/2013
7	5/27/2013
8	7/4/2013
9	9/2/2013
10	10/14/2013
11	11/11/2013
12	11/28/2013
13	12/25/2013
Holidays

 

[fustilugs3]

...If you're filling this formula down, you may want to verify your formula(s) are actually referring to the correct range on the Holidays List.
And apply the absolute references as I did..

HA! I knew I was overlooking something simple. Thank for pointing this out! I appreciate your time!

 

[Jonmo1]

Glad to help, thanks for the feedback.