Excel to Smartsheet WORKDAY formula

IFCC

Here is my excel formula I use to find a FRIDAY shipping date.

=WORKDAY($T1165,-10)-1+(WEEKDAY(WORKDAY($T1165,-10)-1)>=6)*7-WEEKDAY(WORKDAY($T1165,-10)-1)+6

I changed to this in Smartsheet but doesn't work. Any advice?

=workday([start date]12,-10)-1+(weekday(workday([start date]12,-10)-1)>=6)*7-weekday(workday([start date]12,-10)-1)+6

J. Craig Williams

IFCC,

This formula:

=IF(WEEKDAY([Start Date]23) < 7, [Start Date]23 + (6 - WEEKDAY([Start Date]23)), [Start Date]23 + 6)

for row 23, will give you Friday for the week for Sun - Fri and Friday for next week for Saturday.

Craig

 

IFCC

Excellent, thanks.

I missed one note. I need a minimum 10 working days or it returns the following Friday.

That's my -10 in the excel formula.

Also when I put this in the cell. It shows the formula in the cell but doesn't enter?

Still on Smartsheet trial at this point.

thanks

Mike

 

J. Craig Williams

IFCC,

I don't know why you can't enter the formula. That sounds odd.

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.

Craig

 

IFCC

No problem. Appreciate any help.

I can enter the formula on a new sheet just fine.

My Master looks like this

 

J. Craig Williams

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

 Craig

J. Craig Williams

Check for a single apostrophe in front of the equal sign. I don't know why it might be there, but it might.

Craig

IFCC

Yes those dates are all correct.

I do have the single apostrophe and tried deleting it but it always puts it back in.

 

thanks

Mike

J. Craig Williams

Are you an Admin? 

Is Data Validation on?

IFCC

I am Admin as far as I know.

 

Data Validation is where?

IFCC

I'm in the End Date column, maybe that is doing something wrong.

Column Type - is set to  Date/Time but not restricted.

Tried in a new column and it's fine.

 

J. Craig Williams

You can not enter formulas in the date columns used by the Project Settings.

Your Ship Date needs to be another column or you need to turn of Dependencies.

Craig

ps: Data Validation is selected in the Column Type dialog.

 

J. Craig Williams

I think the formula will be very long. Are you OK with a two column solution?

I will pick this up again over the weekend, time permitting.

Craig

IFCC

I've been looking at it and without being able to put the formula in the date column I would have to use another anyway.

So yes, that will be fine.

thanks again

Mike

J. Craig Williams

The solution is posted here:

https://community.smartsheet.com/discussion/calculate-next-calendar-friday#comment-32246