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
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
Comments
-
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
-
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
-
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
-
No problem. Appreciate any help.
I can enter the formula on a new sheet just fine.
My Master looks like this
-
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
-
Check for a single apostrophe in front of the equal sign. I don't know why it might be there, but it might.
Craig
-
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
-
Are you an Admin?
Is Data Validation on?
-
I am Admin as far as I know.
Data Validation is where?
-
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.
-
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.
-
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
-
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
-
The solution is posted here:
https://community.smartsheet.com/discussion/calculate-next-calendar-friday#comment-32246
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 430 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives