Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Dec 28, 2022 02:21 PM - edited Dec 28, 2022 02:24 PM
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!
Solved! Go to Solution.
Dec 28, 2022 03:25 PM
Try this
Dec 28, 2022 03:25 PM
Try this
Dec 29, 2022 07:22 AM
Yes! This worked perfectly, thank you. I wasn't aware of these sort of shortcut formulas for year and week number.