I have 4 dates in 4 different columns. I need to pull a date that lands in this week.

Valeri Nguyen

I have 4 different columns for dates: Phase 1, Phase 2, Phase 3, Phase 4. I am trying to find a formula that can pull any date that falls in the current week and populate that date into a cell. Some fields may have "N/A" in them instead of a date.

Is there a way to do this?

Valeri Nguyen

Paul Newcome

Are you trying to accomplish this across the 4 columns on each row?

Valeri Nguyen

Yes, I would like 1 formula to pull from the 4 columns and provide me 1 date and for this to happen on each row. These columns are not next to each other, but scatter across the sheet.

Paul Newcome

Try something like this...

=IF(WEEKNUMBER([1st Date Column]@row) = WEEKNUMBER(TODAY()), [1st Date Column]@row, IF(WEEKNUMBER([2nd Date Column]@row) = WEEKNUMBER(TODAY()), [2nd Date Column]@row, IF(WEEKNUMBER([3rd Date Column]@row) = WEEKNUMBER(TODAY()), [3rd Date Column]@row, IF(WEEKNUMBER([4th Date Column]@row) = WEEKNUMBER(TODAY()), [4th Date Column]@row))))

Valeri Nguyen

Hmm, I'm getting #Invalid Data.

=IF(WEEKNUMBER([EAS Detection System Install Date]@row) = WEEKNUMBER(TODAY()), [EAS Detection System Install Date]@row, IF(WEEKNUMBER([EAS Deactivator/Detacher Install Date]@row) = WEEKNUMBER(TODAY()), [EAS Deactivator/Detacher Install Date]@row, IF(WEEKNUMBER([CCTV Install Date]@row) = WEEKNUMBER(TODAY()), [CCTV Install Date]@row, IF(WEEKNUMBER([ShopperTrak Install Date]@row) = WEEKNUMBER(TODAY()), [ShopperTrak Install Date]@row, ""))))

Paul Newcome

Make sure all of your Date columns are actually formatted as dates.

Valeri Nguyen

Hi Paul, Yes some of these will be blank and as I noted above that some of these will have N/A in them. I can't change that so I'm trying to figure out a way to also incorporate those criteria into the formula.

Paul Newcome

Try this one...

=IF(ISDATE([1st Date Column]@row), IF(WEEKNUMBER([1st Date Column]@row) = WEEKNUMBER(TODAY()), [1st Date Column]@row, IF(ISDATE([2nd Date Column]@row), IF(WEEKNUMBER([2nd Date Column]@row) = WEEKNUMBER(TODAY()), [2nd Date Column]@row, IF(ISDATE([3rd Date Column]@row), IF(WEEKNUMBER([3rd Date Column]@row) = WEEKNUMBER(TODAY()), [3rd Date Column]@row, IF(ISDATE([4th Date Column]@row), IF(WEEKNUMBER([4th Date Column]@row) = WEEKNUMBER(TODAY()), [4th Date Column]@row))))))))

Valeri Nguyen

The calculation doesn't get past the First Date Range.

=IF(ISDATE([1st Date Column]@row), IF(WEEKNUMBER([1st Date Column]@row) = WEEKNUMBER(TODAY()), [1st Date Column]@row,

*STOPS HERE*

IF(ISDATE([2nd Date Column]@row), IF(WEEKNUMBER([2nd Date Column]@row) = WEEKNUMBER(TODAY()), [2nd Date Column]@row, IF(ISDATE([3rd Date Column]@row), IF(WEEKNUMBER([3rd Date Column]@row) = WEEKNUMBER(TODAY()), [3rd Date Column]@row, IF(ISDATE([4th Date Column]@row), IF(WEEKNUMBER([4th Date Column]@row) = WEEKNUMBER(TODAY()), [4th Date Column]@row))))))))

Paul Newcome

When it stops after the first one, is it leaving a blank?

Valeri Nguyen

Yes, it's blank

Paul Newcome

That's odd. And you definitely have a date in one of those columns that is during the current week number?

Valeri Nguyen

Yes, I did process of elimination, adding dates into each cell.

Paul Newcome

Can you provide a screenshot of the issue as well as copy/paste the exact formula directly from the sheet to here?

Valeri Nguyen

=IF(ISDATE([EAS Detection System Install Date]@row), IF(WEEKNUMBER([EAS Detection System Install Date]@row) = WEEKNUMBER(TODAY()), [EAS Detection System Install Date]@row, IF(ISDATE([EAS Deactivator/Detacher Install Date]@row), IF(WEEKNUMBER([EAS Deactivator/Detacher Install Date]@row) = WEEKNUMBER(TODAY()), [EAS Deactivator/Detacher Install Date]@row, IF(ISDATE([CCTV Install Date]@row), IF(WEEKNUMBER([CCTV Install Date]@row) = WEEKNUMBER(TODAY()), [CCTV Install Date]@row, IF(ISDATE([ShopperTrak Install Date]@row), IF(WEEKNUMBER([ShopperTrak Install Date]@row) = WEEKNUMBER(TODAY()), [ShopperTrak Install Date]@row))))))))