# Calculate ship day of week without using a date

#### nikkilynn2

##### New Member
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!!

### Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

#### WaterGypsy

##### Well-known Member
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

Last edited:

#### JustynaMK

##### Well-known Member
Try WORKDAY formula (cell A1 contains your delivery date):

Code:
``=TEXT(WORKDAY(A1,-3),"dddd")``
Edit: Sorry @WaterGypsy, I just noticed your answer!

Last edited:

#### theBardd

##### Rules violation

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

#### nikkilynn2

##### New Member

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!!

#### nikkilynn2

##### New Member
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!

#### Aryatect

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

ABC
2Monday-3Wednesday

</tbody>
Sheet1

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

</tbody>

<tbody>
</tbody>

Last edited:

Try:

#### nikkilynn2

##### New Member
Thanks Eric! That worked!!

Thank you again everyone! You are life savers!

#### Eric W

##### MrExcel MVP
Glad we could help!

1,101,892
Messages
5,483,536
Members
407,397
Latest member
HerbA

### This Week's Hot Topics

• Finding issue in If elseif else with For each Loop
Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
• MsgBox Error
Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
• CELL FORMAT - IF CONDITION
My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
• Show numbers nearly the same
Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
• Please i need your help to create formula
I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
• Got error while adding column and filter
Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...