# Thread: Calculate ship day of week without using a date Thanks: 0 Likes:  2 Post #5313206 (1)Post #5313260 (1)

1. ## Calculate ship day of week without using a date

Hi! I need a formula or something that will calculate a ship day of the week without using an actual date. For example, my scenario is this:

x load is delivering on a Monday. The transit time from origin is 3 days. What day of the week did the load ship?

The answer would be a Wednesday (don't include weekends in transit time), but I don't know how to make it work in a formula. I am officially stumped. Any help/ideas would be greatly appreciated.

Thanks!!

2. ## Re: Calculate ship day of week without using a date

Have you tried the WORKDAY formula? Enter the Delivery Date as the first parameter, and minus 3 as the 2nd and you should get the Wednesday
....

Sorry just realised you don't want to use dates .... you can use dates bt format the output as the Day Name

3. ## Re: Calculate ship day of week without using a date

Try WORKDAY formula (cell A1 contains your delivery date):

Code:
`=TEXT(WORKDAY(A1,-3),"dddd")`

4. ## Re: Calculate ship day of week without using a date

Code:
`=TEXT("1900-01-0"&--MOD(MATCH(LEFT(A2,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)+B2,7)+1,"dddd")`

5. ## Re: Calculate ship day of week without using a date

Originally Posted by theBardd

Code:
`=TEXT("1900-01-0"&--MOD(MATCH(LEFT(A2,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0)+B2,7)+1,"dddd")`
This is really close.. I had to change the plus signs to minus signs (need to go back in time so to speak... ha). That does bring back the day of the week that I am expecting. I will play around with this... fingers crossed that this may be solved! Thank you everyone!!

6. ## Re: Calculate ship day of week without using a date

Ok.. spoke to soon.. only getting correct results for some of the days. For example, a location that deliveries M-Fr with 2 day transit should bring back the below:

Delivery: Ship day: Formula Result
Mo Th Th
Tu Fr 1900-01-0
We Mo 1900-01-0
Th Tu Su
Fr We Mo

So, only 1 of them was correct. Any ideas?

Appreciate the help!

7. ## Re: Calculate ship day of week without using a date

Hi, a little big formula after tweaking theBardd's formula. Let us know if this works:

A B C
2 Monday -3 Wednesday
Sheet1

Worksheet Formulas
Cell Formula
C2 =TEXT(WORKDAY.INTL(TODAY()-WEEKDAY(TODAY(),2)+MATCH(LEFT(A2,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0),B2,"0000011"),"dddd")

8. ## Re: Calculate ship day of week without using a date

Try:

ABC
1DayDifferenceStart Day
2Mon-3Wed

Sheet1

Worksheet Formulas
CellFormula
C2=INDEX({"Mon","Tue","Wed","Thu","Fri"},MOD(MATCH(A2,{"Mon","Tue","Wed","Thu","Fri"},0)+B2-1,5)+1)

9. ## Re: Calculate ship day of week without using a date

Thanks Eric! That worked!!

Thank you again everyone! You are life savers!