Help

Save the date! Join us on October 16 for our Product Ops launch event. Register here.

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

Solved
Jump to Solution
890 2
cancel
Showing results for 
Search instead for 
Did you mean: 
kts
5 - Automation Enthusiast
5 - Automation Enthusiast

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!

1 Solution

Accepted Solutions
John_B2
6 - Interface Innovator
6 - Interface Innovator

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" ))))

See Solution in Thread

2 Replies 2
John_B2
6 - Interface Innovator
6 - Interface Innovator

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
5 - Automation Enthusiast
5 - Automation Enthusiast

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