Calculate ship day of week without using a date

nikkilynn2

New Member
Joined
May 2, 2016
Messages
12
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!!
 

Some videos you may like

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
Joined
Jan 15, 2010
Messages
697
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
Joined
Aug 28, 2016
Messages
647
Office Version
365, 2013
Platform
Windows
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
Joined
Jan 21, 2012
Messages
912
How about this?

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
Joined
May 2, 2016
Messages
12
How about this?

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
Joined
May 2, 2016
Messages
12
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
Joined
Jun 17, 2019
Messages
308
Hi, a little big formula after tweaking theBardd's formula. Let us know if this works:

ABC
2Monday-3Wednesday

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
</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")

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 
Last edited:

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,871
Try:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Day</td><td style=";">Difference</td><td style=";">Start Day</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Mon</td><td style="text-align: right;;">-3</td><td style=";">Wed</td></tr></tbody></table><p style="width:4.8em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C2</th><td style="text-align:left">=INDEX(<font color="Blue">{"Mon","Tue","Wed","Thu","Fri"},MOD(<font color="Red">MATCH(<font color="Green">A2,{"Mon","Tue","Wed","Thu","Fri"},0</font>)+B2-1,5</font>)+1</font>)</td></tr></tbody></table></td></tr></table><br />
 

Watch MrExcel Video

Forum statistics

Threads
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...
Top