Monday of date

Mark Capretta

I have an installation date column and a "week of" column. In our "week of" column, we keep the date of the Monday before.

As an example, if the installation date is 1/19/17, the "Week of" column should be 1/16/17. Or, if the date is on a Monday, (Installation 1/23/17), the "Week of" column should be the same as the installation column (Week of 1/23/17).

 

I need a formula that would allow me to just fill in the installation date, and will automatically populate the "Week of" date. Every formula I have tried fails when the date is a Monday, so if I filled in 1/23/17, it will populate the Monday prior (1/16/17).

Thanks!

J. Craig Williams

Post your current formula. You are almost there.

Craig

 

Mark Capretta

=[Installation Date]-WEEKDAY([Installation Date]-2)

J. Craig Williams

This is one way:

 

=IF(WEEKDAY([complaint date]29) = 2, [complaint date]29, [complaint date]29 - WEEKDAY([complaint date]29 - 2))

 

I think I have a more elegant way but need to find a pencil.

Craig

J. Craig Williams

Found one.

 

=[complaint date]37 - WEEKDAY([complaint date]37 - 1) + 1

 

Craig

 

Mark Capretta

Follow-up that I should have mentioned. I'm trying to pull the date from a separate cell. Here's the exact copy of me trying your provided formulas:

 

=IF(WEEKDAY(([Est Install Start Dt]1783)29) = 2, ([Est Install Start Dt]1783)29, (([Est Install Start Dt]1783)29) - (WEEKDAY(([Est Install Start Dt]1783)29) - 2)

 

=([Est Install Start Dt]1783)37-WEEKDAY(([Est Install Start Dt]1783)37-1)+1

 

I'm not having any luck with that. Does that new information change anything?

J. Craig Williams

My final formula:

 

=[complaint date]37 - WEEKDAY([complaint date]37 - 1) + 1

 

Ignore the first one, that was the inelegant one.

 

 

=CELLREFERENCE - WEEKDAY(CELLREFERENCE - 1) + 1

 

so for your example, the cell reference is [Est Install Start Dt]1783

 

=[Est Install Start Dt]1783 - WEEKDAY([Est Install Start Dt]1783 - 1) + 1

 

Craig