Formula for calculating the Monday before a date?

RAdamowicz

Hi all!

I'm trying to create a formula that will auto-populate due dates in a tracker that we have for new hires. In one location, their paperwork must be complete by the Monday before their start date. In another location, it must be complete by the Wednesday before their start date. They may start on different days of the week depending on their job, so I can't just do "Start Date - X days". Does anyone have any thoughts on how I could do this?

Here's an example:

For people starting at our headquarters, I need their paperwork due date to be the Monday before they start, no matter what day of the week they start on. For people in our satellite office, it needs to be the Wednesday prior to their start date, no matter what day they start on.

Any ideas? Thanks in advance!

NickStaffordPM

Try this one?

=IF([Ready]@row=1, (IF(Location@row = "Headquarters", IF(WEEKDAY([Start Date]@row) = 2, [Start Date]@row - 7, IF(WEEKDAY([Start Date]@row) = 3, [Start Date]@row - 1, IF(WEEKDAY([Start Date]@row) = 4, [Start Date]@row - 2, IF(WEEKDAY([Start Date]@row) = 5, [Start Date]@row - 3, IF(WEEKDAY([Start Date]@row) = 6, [Start Date]@row - 4, IF(WEEKDAY([Start Date]@row) = 7, [Start Date]@row - 5, [Start Date]@row - 6)))))), IF(WEEKDAY([Start Date]@row) = 1, [Start Date]@row - 2, IF(WEEKDAY([Start Date]@row) = 2, [Start Date]@row - 3, IF(WEEKDAY([Start Date]@row) = 3, [Start Date]@row - 4, IF(WEEKDAY([Start Date]@row) = 5, [Start Date]@row - 6, IF(WEEKDAY([Start Date]@row) = 4, [Start Date]@row - 5, IF(WEEKDAY([Start Date]@row) = 6, [Start Date]@row - 7, IF(WEEKDAY([Start Date]@row) = 7, [Start Date]@row - 1, "Error"))))))))),"Not Ready")

If this doesnt work, what error do you get?

ionam

I asked AI and got this:

To calculate the due date as the Monday before a given start date in Smartsheet, you can use the WORKDAY function in combination with WEEKDAY. The WEEKDAY function will help you determine the day of the week for the start date, and then you can adjust accordingly to find the previous Monday.

Here's how to create the formula:

=IF([Start Date]@row <> "", [Start Date]@row - WEEKDAY([Start Date]@row, 2), "")

Explanation:

• [Start Date]@row: This references the start date for the current row.
• WEEKDAY([Start Date]@row, 2): This returns a number from 1 (Monday) to 7 (Sunday) based on the start date. With the type set to 2, Monday is considered the first day of the week.
• By subtracting the result of WEEKDAY from the start date, you effectively get the previous Monday.
• The IF condition ensures that the output is blank if the start date is empty.

Example:

• If your start date is 2023-10-10 (Tuesday), the formula will determine that the previous Monday is 2023-10-09.

You can replace [Start Date] in the formula with the actual name of your start date column.

Hope it works for you.

RAdamowicz

Hmm… Unfortunately that returns an incorrect argument error, but thanks for trying! I am going to try and play with this a bit more to see if I can figure it out.

NickStaffordPM

Hello,

Try this out, make the following formula the column formula for Paperwork Due:

=IF(WEEKDAY([Start Date]@row) = 2, [Start Date]@row - 7, IF(WEEKDAY([Start Date]@row) = 3, [Start Date]@row - 1, IF(WEEKDAY([Start Date]@row) = 4, [Start Date]@row - 2, IF(WEEKDAY([Start Date]@row) = 5, [Start Date]@row - 3, IF(WEEKDAY([Start Date]@row) = 6, [Start Date]@row - 4, IF(WEEKDAY([Start Date]@row) = 7, [Start Date]@row - 5, [Start Date]@row - 6))))))

I built an example to show how it would work below

If this doesn't help, let me know!

RAdamowicz

Hi @NickStaffordPM ! Thanks for this! That's at least a great start 😊

The problem I am having is that we have variable due dates depending on their location. Here's the whole formula I'm trying now - it's correctly returning a Monday due date if the location is our headquarters, but is failing to return any date for our satellite location. (Note two errors from my first post - I forgot to add that this should only show up if a "Ready" box is checked, indicating the rest of their hire is completed on our end, and I have also been corrected by the colleague I'm building this for - the satellite office's paperwork is due Fridays, not Wednesdays).

=IF(Ready@row = 1, IF(Location@row = "Headquarters", IF(WEEKDAY([Start Date]@row) = 2, [Start Date]@row - 7, IF(WEEKDAY([Start Date]@row) = 3, [Start Date]@row - 1, IF(WEEKDAY([Start Date]@row) = 4, [Start Date]@row - 2, IF(WEEKDAY([Start Date]@row) = 5, [Start Date]@row - 3, IF(WEEKDAY([Start Date]@row) = 6, [Start Date]@row - 4, IF(WEEKDAY([Start Date]@row) = 7, [Start Date]@row - 5, [Start Date]@row - 6)))))))), (IF(Location@row = "Satellite Office", IF(WEEKDAY([Start Date]@row) = 2, [Start Date]@row - 3, IF(WEEKDAY([Start Date]@row) = 3, [Start Date]@row - 5, IF(WEEKDAY([Start Date]@row) = 4, [Start Date]@row - 6, IF(WEEKDAY([Start Date]@row) = 5, [Start Date]@row - 7, IF(WEEKDAY([Start Date]@row) = 6, [Start Date]@row - 1, IF(WEEKDAY([Start Date]@row) = 7, [Start Date]@row - 2, [Start Date]@row - 3))))))), "")

Any thoughts? If I can get this working, I can modify it to fill in quite a few more date columns (orientation day, training, etc) that would cut down on a lot of manual data entry for my colleague, but it's definitely being cranky. I asked AI to fix this and got nothing useful back, so hoping you or someone else can help me. Thanks!

NickStaffordPM

Try this one?

=IF([Ready]@row=1, (IF(Location@row = "Headquarters", IF(WEEKDAY([Start Date]@row) = 2, [Start Date]@row - 7, IF(WEEKDAY([Start Date]@row) = 3, [Start Date]@row - 1, IF(WEEKDAY([Start Date]@row) = 4, [Start Date]@row - 2, IF(WEEKDAY([Start Date]@row) = 5, [Start Date]@row - 3, IF(WEEKDAY([Start Date]@row) = 6, [Start Date]@row - 4, IF(WEEKDAY([Start Date]@row) = 7, [Start Date]@row - 5, [Start Date]@row - 6)))))), IF(WEEKDAY([Start Date]@row) = 1, [Start Date]@row - 2, IF(WEEKDAY([Start Date]@row) = 2, [Start Date]@row - 3, IF(WEEKDAY([Start Date]@row) = 3, [Start Date]@row - 4, IF(WEEKDAY([Start Date]@row) = 5, [Start Date]@row - 6, IF(WEEKDAY([Start Date]@row) = 4, [Start Date]@row - 5, IF(WEEKDAY([Start Date]@row) = 6, [Start Date]@row - 7, IF(WEEKDAY([Start Date]@row) = 7, [Start Date]@row - 1, "Error"))))))))),"Not Ready")

If this doesnt work, what error do you get?

RAdamowicz

@NickStaffordPM that worked! You are amazing, thank you! I'm not sure I entirely understand the way it is written - why is the last one "Error". I'm confused that we didn't have to add and IF for the satellite location but I'll take it!

NickStaffordPM

Basically we have it set up to do one option, OR any other option - and since you only have two possible selections, the any other option category just represents the Satiellite Location (Boolean, 1 or 0). If you ever had to add a third location, you would need to make some significant changes to account for that :)

RAdamowicz

Thank you!