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

âś­âś­
edited 02/12/21

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?

• âś­âś­
edited 02/12/21
• âś­âś­âś­âś­âś­âś­

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

• âś­âś­

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.

• âś­âś­âś­âś­âś­âś­

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

• âś­âś­

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

• âś­âś­âś­âś­âś­âś­

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

• âś­âś­

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.

• âś­âś­âś­âś­âś­âś­

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

• âś­âś­

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

• âś­âś­âś­âś­âś­âś­

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

• âś­âś­

Yes, it's blank

• âś­âś­âś­âś­âś­âś­

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

• âś­âś­

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

• âś­âś­âś­âś­âś­âś­

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

• âś­âś­

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

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!