Welcome!

calculate next calendar friday

Hello, I am trying to come up with a formula that will calculate the next calendar friday after a minimum 10 working days from my processing date.

I've had help from Craig to get this formula.

How can I modify this formula to accommodate the minimum 10 working days before returning the next Friday?

=IF(WEEKDAY(Orderby6) < 7, Orderby6 + (6 - WEEKDAY(Orderby6)), Orderby6 + 6)

thanks

Mike

Find more posts tagged with

Comments

J. Craig Williams

Mike,

Here's the post where we were working on it.

https://community.smartsheet.com/discussion/excel-smartsheet-workday-formula

We got side-tracked with your data validation problem and you did not answer my questions here:

https://community.smartsheet.com/comment/30346

I also got distracted by customers.

I may have a solution, but want to see the answers to the questions so I don't need to do it again. (No 2nd column)

Or if someone else has the solution, that would be great too.

One more question:

Isn't [OrderBy] the result, not the input? Shouldn't the input be something like [ProcessDate]?

Craig

IFCC

My apologies Craig. I thought I had answered your questions.

"By 10 days do you mean if [Start Date] is Wednesday, next Friday the ship date is the following Friday?

That is for June 21st (Wednesday), the ship date would be July 7th because June 30th is only 9 days away?

UPDATE: You said working days. I need to think of my response. I may not get to it right away."

--correct, the supplier needs a minimum 10 work days, not counting the order date or ship date

"A few more questions:

1. Are the Start Date and the Friday shipping date both one of the 10 working days?

That is, Monday July 10th 2017 ships Friday July 21st 2017

2. Are these shipping dates correct for no holiday on July 3rd or 4th?

Start Date Ship Date

2017-06-22 / 26 2017-07-07

2017-06-27 / 30 2017-07-14

3. Are these shipping dates correct for one holiday on July 3rd or 4th?

Start Date Ship Date

2017-06-22 / 23 2017-07-07

2017-06-24 / 30 2017-07-14"

1. Start date and Friday Ship Date are not one of the 10 working days.

2. 2017-06-22 - ship date 2017-07-07, 2017-06-26 - ship date would be 2017-07-14

2017-06-27 - ship date 2017-07-14, 2017-06-30 - ship date would be 2017-07-21

3. 2017-06-22 / 23 , both would be ship date 2017-07-14

2017-06-24 - ship date 2017-07-14 , 2017-06-30 - ship date would be 2017-07-21

I was working on it last night and came up with this. Seems to work (process of elimination with the Days column) Not sure if it will work in all cases though.

"One more question:

Isn't [OrderBy] the result, not the input? Shouldn't the input be something like [ProcessDate]?"

[orderby] is the process date, and I have a column [receive] that would be the friday shipdate.

Thanks for your efforts so far, I know this isn't an easy answer. Trying to get this together before my trial runs out so I can present to owners the usefulness this software will be.

Mike

J. Craig Williams

Mike,

Try this (row 23)

=IF(WEEKDAY(Orderby23) < 7, Orderby23 + (6 - WEEKDAY(Orderby23)), Orderby23 + 6)

The hard part was requirements gathering, not the actual solution.

I hope this give you what you are looking for.

Craig

IFCC

Thanks for your help Craig. Appreciate your time.

Mike