Due to production and shipping issues my job has created a report that has the customer's need by date and if a product has been completed or not. If an order is complete we need to determine when it will ship out and arrive to the customer. We have a set shipping schedule and orders get shipped on the truck right before the need by date. For example our truck leaves on Monday and Friday every week so if an order is complete and the need by date is on a Wednesday it will ship on Monday so any order with a need by date from Tuesday to Friday gets shipped the Monday before and any need by date from Saturday to Monday gets shipped on Friday. Now my questions is, is there a way to have a date range yield it's shipping date? Example when I'm writing this it's 5/11/2022 so if I have completed orders with need by dates of 5/12 and 5/15 all of which should be on the 5/13 truck could I use a formula to fill in the shipping date for those need by dates? We've been doing it manually and although determining the dates isn't difficult it's time consuming when each report has over 3000 lines and there is a report for each customer. If it's not possible then we won't have a choice but I would like to hope so. Please help.
Asked
Active
Viewed 80 times
1 Answers
-1
The third column is just to show that the ship dates are on the correct day. It looks different because the dates are Australian (DD/MM/YYYY) rather than American (MM/DD/YYYY), but that shouldn't matter for the function to work.
The function in the Ship Date column:
=IF(OR(TEXT(A8,"dddd")="Tuesday",TEXT(A8,"dddd")="Wednesday",TEXT(A8,"dddd")="Thursday",TEXT(A8,"dddd")="Friday"),A8-WEEKDAY(A8,3),A8-WEEKDAY(A8+2,2))
Need By Date (including header) = A7:A20 Ship Date (including header) = B7:B20
Explanation
- TEXT(value, format_text): this is used to convert the date (DD/MM/YYYY) to the long day (e.g. Monday)
Breakdown
- IF the day is Tues, Wed, Thurs, or Fri
- THEN return the date of that weeks Monday
- ELSE return the date of that weeks Friday
JaxValentine
- 22
- 2