Calculate ship day of week without using a date

[nikkilynn2]

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

 

[WaterGypsy]

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

 

[JustynaMK]

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

=TEXT(WORKDAY(A1,-3),"dddd")

Edit: Sorry @WaterGypsy, I just noticed your answer!

 

[theBardd]

How about this?

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

 

[nikkilynn2]

How about this?

=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]

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]

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

	A	B	C
Monday	Wednesday

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]2[/TD]

[TD="align: right"]-3[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]C2[/TH]
[TD="align: left"]=TEXT(WORKDAY.INTL(TODAY()-WEEKDAY(TODAY(),2)+MATCH(LEFT(A2,2),{"Mo","Tu","We","Th","Fr","Sa","Su"},0),B2,"0000011"),"dddd")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Eric W]

Try:

Book1
	A	B	C
1	Day	Difference	Start Day
2	Mon	-3	Wed
Sheet1

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

 

[nikkilynn2]

Thanks Eric! That worked!!

Thank you again everyone! You are life savers!

 

[Eric W]

Glad we could help!