Automatically Populate Monday before Existing Date in Sheet
Hi all,
I've seen similar requests on these forums but unfortunately I have not yet been able to make this work.
I have a project schedule with Start Date and End Date columns. My leadership would like to see two associated columns that show the Monday before the Start Date and End Date to help with planning - so they can see everything starting and coming due in a given week.
Our initial idea was to simply manually enter this but as dates shift over time and the schedule grows, this will get complicated.
Can someone help with the formula that will essentially do "If X date, show prior Monday date unless X date is Monday, then just show X date."
Comments
-
Try this:
=IF(ISDATE(Date@row), IF(WEEKDAY(Date@row) = 1, Date@row - 6, Date@row - WEEKDAY(Date@row) + 2))
Craig
-
Hi Craig,
I'm getting an "# INVALID COLUMN VALUE" when I try to do this.
The start dates are in a column called "Start Date" so if I try to do this in a new column in row 165 I currently have:
=IF(ISDATE([Start Date]165), IF(WEEKDAY([Start Date]165) = 1, [Start Date]165 - 6, [Start Date]165 - WEEKDAY([Start Date]165) + 2))
Any thoughts on where my mis-step is?
-
Is the column containing the formula also a Date?
Craig
-
Continued thanks to you for your prompt replies and help on this!
I would like to be the column to contain a date automatically. A picture is worth 1000 words. In the attachment, I'd like "Starting Date Week (Monday)" to automatically populate based on column "Start Date." The fields are populated correctly in the attachment, but entered manually. Hope this helps.
-
The formula resides in [Starting Date Week (Monday)] column.
It appears based on the display 6/11/18, that the column is of type Text/Number.
It needs to be type of Date.
Craig
-
This worked! Thank you.
-
Nice on Craig, I love your formula solutions....
RichardR
-
Hi Craig! What would I need to change within this formula to make it for Friday prior, rather than Monday? Still learning :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!