I have 4 dates in 4 different columns. I need to pull a date that lands in this week.
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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!