Automatically Populate Monday before Existing Date in Sheet

Matt.Robinson

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." 

J. Craig Williams

Try this:

=IF(ISDATE(Date@row), IF(WEEKDAY(Date@row) = 1, Date@row - 6, Date@row - WEEKDAY(Date@row) + 2))

Craig

Matt.Robinson

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? 

J. Craig Williams

Is the column containing the formula also a Date?

Craig

Matt.Robinson

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. 

 

J. Craig Williams

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

Matt.Robinson

This worked! Thank you. 

J. Craig Williams

Richard Rymill SBP

Nice on Craig, I love your formula solutions....

 

RichardR

KatieFields

Hi Craig! What would I need to change within this formula to make it for Friday prior, rather than Monday? Still learning :)