#### 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.

# Excel to Smartsheet WORKDAY formula

Options
edited 12/09/19

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

• ✭✭✭✭✭✭
Options

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

• Options

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

• ✭✭✭✭✭✭
edited 06/23/17
Options

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

• Options

No problem. Appreciate any help.

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

My Master looks like this

• ✭✭✭✭✭✭
Options

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

• ✭✭✭✭✭✭
edited 06/23/17
Options

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

Craig

• Options

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

• ✭✭✭✭✭✭
Options

Is Data Validation on?

• Options

I am Admin as far as I know.

Data Validation is where?

• edited 06/23/17
Options

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.

• ✭✭✭✭✭✭
edited 06/23/17
Options

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.

• ✭✭✭✭✭✭
edited 06/23/17
Options

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

• Options

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

• ✭✭✭✭✭✭
Options
This discussion has been closed.