Help with WEEKDAY/Bank Holidays

Georgina Thurlow

Hi there,

Hoping someone can help. I am wanting to check a date (held in row [3rd Party Pay Day] to make sure that it's not a weekend or a bank holiday. I can get the first half of the formula working but not when I add in the check against the bank holiday:

=IFERROR(IF(OR(OR(WEEKDAY([3rd Party Pay Day]5) = 1, WEEKDAY([3rd Party Pay Day]5) = 7, CONTAINS([BH 1]@row, [3rd Party Pay Day]@row), "No", "Yes"), "N/A")))

It would be fantastic too if there was a way to find the closest working day if the date falls on a bank holiday or weekend (earlier than the pay day rather than later).

Thanks in advance,

Georgina

Genevieve P.

Hi @Georgina Thurlow

The IF Statement is getting stopped at the CONTAINS function with your range and dates. Instead of looking through the three cells as one range to check for that specific date, you'll want to compare each cell to the date to see if it's the same value as a logic statement within your OR function.

Try this:

=IF(OR(WEEKDAY([3rd Party Pay Day]@row) = 1, WEEKDAY([3rd Party Pay Day]@row) = 7, [BH 1]@row = [3rd Party Pay Day]@row, [BH 2]@row = [3rd Party Pay Day]@row, [BH 3]@row = [3rd Party Pay Day]@row), "No", "Yes")

Then in regards to returning the previous Work Day, you can use the WORKDAY function!

Since you're already setting up this helper column with the "No" or "Yes", you can say that if this column says "No", return the previous working day:

=IF([Pay Day - Valid Weekday?]@row = "No", WORKDAY([3rd Party Pay Day]@row, -1))

Of course, you could build this in to your current formula as the output instead, if you'd prefer:

=IF(OR(WEEKDAY([3rd Party Pay Day]@row) = 1, WEEKDAY([3rd Party Pay Day]@row) = 7, [BH 1]@row = [3rd Party Pay Day]@row, [BH 2]@row = [3rd Party Pay Day]@row, [BH 3]@row = [3rd Party Pay Day]@row), WORKDAY([3rd Party Pay Day]@row, -1), [3rd Party Pay Day]@row)

Note that this would need to be pasted into a Date-Type of column.

Did this work for you?

Cheers!

Genevieve

Abraham Sanchez

Hi Georgina,

I believe your formula won't work because of the data type, you BH 1 seems it a text field, and your 3rd PArty Pay Day a date field.

a posible solution to try out is the following

1) create a sheet that has all the holidays, one on each row. you may name the field Holiday.

2) on your work sheet now, us this formula

IFERROR(

IF(

OR(

WEEKDAY([3rd Party Pay Day]@row) = 1,

WEEKDAY([3rd Party Pay Day]@row) = 7,

IFERROR(Match( [3rd Party Pay Day]@row,{Link to Holiday}),0)>0

),"No","Yes"

)

,"N/A")

Note: when selecting the range in the Match formula, cick the Reference Anohter Sheet, so you can select you holiday sheet, and the holiday column as your range.

Hope I made all sintax correct.

Georgina Thurlow

Hi Abraham,

Thank you so much for getting back to me.

BH1 and 3rd Party Pay Days are both already formatted correctly to Date columns.

I am not sure that the Match formula would work as the bank holiday is dependent on the country of the payment rather than the 3rd party due date.

I tried updating to this formula: =IF(OR(WEEKDAY([3rd Party Pay Day]@row) = 1, WEEKDAY([3rd Party Pay Day]@row) = 7, CONTAINS([3rd Party Pay Day]@row, [BH 1]@row:[BH 3]@row)), "No", "Yes")

This looks like its working and doesn't error but it seems like the 2nd half of the formula isn't doing what it should as even though I have labelled 15/03 as a bank holiday, it is saying that it is still returning "Yes".

Smartsheet Bank Holidays.JPG

Note that the dates are correctly formatted and another formula that I have entered works =IF([3rd Party Pay Day]@row = [BH 1]@row, "Y", "N") (returns "Y").

Thanks,

Georgina

Genevieve P.

Hi @Georgina Thurlow

The IF Statement is getting stopped at the CONTAINS function with your range and dates. Instead of looking through the three cells as one range to check for that specific date, you'll want to compare each cell to the date to see if it's the same value as a logic statement within your OR function.

Try this:

=IF(OR(WEEKDAY([3rd Party Pay Day]@row) = 1, WEEKDAY([3rd Party Pay Day]@row) = 7, [BH 1]@row = [3rd Party Pay Day]@row, [BH 2]@row = [3rd Party Pay Day]@row, [BH 3]@row = [3rd Party Pay Day]@row), "No", "Yes")

Then in regards to returning the previous Work Day, you can use the WORKDAY function!

Since you're already setting up this helper column with the "No" or "Yes", you can say that if this column says "No", return the previous working day:

=IF([Pay Day - Valid Weekday?]@row = "No", WORKDAY([3rd Party Pay Day]@row, -1))

Of course, you could build this in to your current formula as the output instead, if you'd prefer:

=IF(OR(WEEKDAY([3rd Party Pay Day]@row) = 1, WEEKDAY([3rd Party Pay Day]@row) = 7, [BH 1]@row = [3rd Party Pay Day]@row, [BH 2]@row = [3rd Party Pay Day]@row, [BH 3]@row = [3rd Party Pay Day]@row), WORKDAY([3rd Party Pay Day]@row, -1), [3rd Party Pay Day]@row)

Note that this would need to be pasted into a Date-Type of column.

Did this work for you?

Cheers!

Genevieve

Georgina Thurlow

Thank you so much Genevieve! This is exactly what I needed and worked perfectly :)

Georgina