Need help to adjust automatically my Order Date using Date Formula

Benn

Hi,

I have two date columns in my sheet 1st column is "First Order Date" and the next one is the "Next Order Date" with underlying formula of =[First Order Date]@row+ 30, we need to supply our clients every 30 days.

The problem here is that using that formula "=[First Order Date]@row+ 30" will give you all the day in a week and we don't want it to fall on Saturday and Sunday since we don't have operations every weekend. Now, is there a formula where , when the "=[First Order Date]@row+ 30" falls to Saturday, it will be adjusted to the date of Friday and when it falls to Sunday it will be adjusted to the date of Monday.

Kindly help me to formulate a formula and help me if I need some adjustment to my sheet.

Steven Stikons

Hi Benn,

You can use the WEEKDAY function to check for the day of the week that your First Order Date + 30 is. The Smartsheet WEEKDAY result for Saturday is 7 and for Sunday is 8. You can use this to write a formula to add 32 days to the First Order Date if it's Saturday and to add 31 days if it's Sunday. For any other day the formula would add 30.

Here is the formula I'm recommending. Let me know if this works:

=IF(WEEKDAY([First Order Date]@row + 30) = 7, [First Order Date]@row + 32, IF(WEEKDAY([First Order Date]@row + 30) = 8, [First Order Date]@row + 31, [First Order Date]@row + 30))

Leibel S

@Benn

The below should solve your issue.

=[First Order Date]@row + 30 + IF(WEEKDAY([First Order Date]@row) = 5, -1, 0) + IF(WEEKDAY([First Order Date]@row) = 6, 1, 0)

Steven Stikons

Hi Benn,

You can use the WEEKDAY function to check for the day of the week that your First Order Date + 30 is. The Smartsheet WEEKDAY result for Saturday is 7 and for Sunday is 8. You can use this to write a formula to add 32 days to the First Order Date if it's Saturday and to add 31 days if it's Sunday. For any other day the formula would add 30.

Here is the formula I'm recommending. Let me know if this works:

=IF(WEEKDAY([First Order Date]@row + 30) = 7, [First Order Date]@row + 32, IF(WEEKDAY([First Order Date]@row + 30) = 8, [First Order Date]@row + 31, [First Order Date]@row + 30))

Leibel S

@Benn

The below should solve your issue.

=[First Order Date]@row + 30 + IF(WEEKDAY([First Order Date]@row) = 5, -1, 0) + IF(WEEKDAY([First Order Date]@row) = 6, 1, 0)