Run IF(OR()) if any of 4 fields is filled

[kts]

Hello! I have 4 date fields, and for a given record, any of the 4 could be filled in (or several of them, or all of them). I've written a formula to return 'Y' if any of the 4 dates fall within the current week. Here's what I've got:

IF(
OR(
{Date 1}, {Date 2}, {Date 3}, {Date 4},
IF(
OR(
((DATETIME_FORMAT({Date 1}, 'YYYY')&DATETIME_FORMAT({Date 1}, 'WW'))) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW')),
((DATETIME_FORMAT({Date 2}, 'YYYY')&DATETIME_FORMAT({Date 2}, 'WW')) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW'))),
((DATETIME_FORMAT({Date 3}, 'YYYY')&DATETIME_FORMAT({Date 3}, 'WW')) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW'))),
((DATETIME_FORMAT({Date 4}, 'YYYY')&DATETIME_FORMAT({Date 4}, 'WW')) = (DATETIME_FORMAT(TODAY(), 'YYYY')&DATETIME_FORMAT(TODAY(), 'WW')))
),
'Y', 'N'
)))

I added the first IF(OR( argument to allow the second IF(OR( to run, no matter how many of the date fields have data in them.

However, the same thing happens no matter if I have that first argument or not: if any of the fields is blank, I get #ERROR, and if they're all filled, I just get a blank field, regardless of whether any of the dates falls within the current week.

I've tried various permutations of this formula and can't seem to get it to work. Can anybody pinpoint what I'm doing wrong here? Thank you!

[John_B2 · Accepted solution]

Try this

IF( IS_SAME( YEAR(TODAY()) & WEEKNUM(TODAY()) , YEAR({Date 1}) & WEEKNUM({Date 1}))

      , "Y", 

    IF( IS_SAME( YEAR(TODAY()) & WEEKNUM(TODAY()) , YEAR({Date 2}) & WEEKNUM({Date 2}))

      , "Y",

      IF( IS_SAME( YEAR(TODAY()) & WEEKNUM(TODAY()) , YEAR({Date 3}) & WEEKNUM({Date 3}))

      , "Y",

          IF( IS_SAME( YEAR(TODAY()) & WEEKNUM(TODAY()) , YEAR({Date 4}) & WEEKNUM({Date 4}))

        , "Y",

      "N" ))))

[kts]

Yes! This worked perfectly, thank you. I wasn't aware of these sort of shortcut formulas for year and week number.