#### Welcome to the Smartsheet Forum Archives

The posts in this forum are no longer monitored for accuracy and their content may no longer be current. If there's a discussion here that interests you and you'd like to find (or create) a more current version, please Visit the Current Forums.

# Monday of date

edited 12/09/19

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!

• ✭✭✭✭✭✭

Post your current formula. You are almost there.

Craig

• edited 01/19/17

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

• ✭✭✭✭✭✭

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

• ✭✭✭✭✭✭

Found one.

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

Craig

• 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?

• ✭✭✭✭✭✭

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

This discussion has been closed.