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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,922
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,102,889
Messages
5,489,546
Members
407,697
Latest member
Lotte_4

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top