Using WORKDAYS formula with "Holiday" range - not calculating correctly

[SelinaR]

Hiya

I have read through the forums, but can't seem to find an answer to my unique situation....

Action: I need to calculate 5 working days after a date and include holidays
Formula: Using workdays formula and named my holiday range pubhol18 (I have also tried just selecting the range)
Scenario: 5 working days from 27 December (including 1st of Jan (holiday) = 04/01/2018
Issue: it's coming back as 03/01/2018?
**PS: I've made sure all my dates are in a date format

Calculation:
[TABLE="width: 500"]
<tbody>[TR]
[TD]27/12/2017[/TD]
[TD]=workdays(A1,5,pubhol18)[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

THANKS IN ADVANCE
Selina

 

[steve the fish]

That suggests to me that excel is not seeing your holiday date within the period it needs to ignore. WORKDAY does produce 4/1/2018 for the conditions you prescribe.

 

[SelinaR]

Hiya
I have tried the WORKDAYS formula without the holiday range for 5 days after 27th of December 2018 and it's still returning to 3rd of Jan 2019
=workday(A1,5)
(A1 being 27/12/2018)

 

[steve the fish]

Thats what id expect. The 28th, 31st, 1st, 2nd, 3rd.

 

[SelinaR]

It musn't see the 1st as a working day, as it's a public holiday here in Australia.

 

[jtakw]

Hi,

Double check the Year of the Holiday in your holiday range (i.e. 1/1/2019, and Not 1/1/2018)

 

[SelinaR]

OMG... yeah - they year was incorrect - all working now #dumbass I copied and pasted the P/Holidays but didn't update the year
thanks for your help - i though I was loosing my mind!

 

[jtakw]

You're welcome, that happens to all of us...