Formula to deduct calender days from a date, holding weekends in account
for example:
date is 09/06 - need to deduct 10 calenderdays, the date returned may never be saterday or sunday.
the outcome must be 01/06 - since 10 calenderday returns Sunday 31/05 - which is not allowed.
Answers
-
Try something along the lines of...
=WORKDAY([Date Column]@row, -10)
-
Already tried that,
the result is then :
=WORKDAY([SAILING DATE]1; -STORAGE1)= 26/05
(sailing date being 9th of June & storage is-10)
For some more background, the setup is to have a formula in which a date is shown when we can deliver free of charge on a terminal. problem being is that various carriers have different allowed days (ranges from 2 to 10) since weekend are not operational working days, the outcome can never be in a weekend. so the date shown must be (if in a weekend) revert back to the closest date within the allowed days, in this example the first of June
-
The problem is that you are subtracting the data in the cell and the data happens to be a negative number (minus a negative is the same as plus). Try using just a cell reference to the negative number which basically adds a negative which is the same as subtracting.
-
Hi Paul,
so gratefull for your help, but the problem remains the same,
formula applied: =WORKDAY([SAILING DATES]1; -STORAGE1)
since free delivery on 29/05 means a total of 15 calenderdays, not 10
It's a brainbreaker this one..
-
Ok. Let's try a different approach...
You want to subtract 10 calendar days, but if it falls on a weekend, you need a weekday.
If it falls on a Saturday, do you want to move to the previous Friday or the following Monday? Same for if it falls on a Sunday?
-
Hi Paul,
exactly,
if it falls on a saterday or sunday, it must move to monday
since that falls within the granted 'freetime' of 10 calenderdays
-
Give this a try...
=[Sailing Dates]@row - 10 + IF(WEEKDAY([Sailing Dates]@row - 10) = 7, 2, IF(WEEKDAY([Sailing Dates]@row - 10) = 1, 1))
-
Hi Paul,
we're getting closing!
but when you change the sailing date to 12/06, it goes wrong again.
the outcome changes to 02/06 which are 11 calenderdays instead of 10
or if you change the sailing date to 08/06 - the outcome changes to 29/05 - which are 11 calender days and should go to 01/06
Can i maybe invite you in my sheet?
-
That seems odd... Try this...
=([Sailing Dates]@row - 10) + IF(WEEKDAY([Sailing Dates]@row - 10) = 7, 2, IF(WEEKDAY([Sailing Dates]@row - 10) = 1, 1))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!