I have 4 dates in 4 different columns. I need to pull a date that lands in this week.
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?
Answers
-
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.4K Get Help
- 364 Global Discussions
- 200 Industry Talk
- 429 Announcements
- 4.4K Ideas & Feature Requests
- 137 Brandfolder
- 129 Just for fun
- 128 Community Job Board
- 446 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 284 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!