Calculate ship day of week without using a date
Results 1 to 10 of 10

Thread: Calculate ship day of week without using a date

  1. #1
    New Member
    Join Date
    May 2016
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #2
    Board Regular WaterGypsy's Avatar
    Join Date
    Jan 2010
    Location
    London, England
    Posts
    690
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default 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
    Last edited by WaterGypsy; Jul 22nd, 2019 at 09:55 AM.

  3. #3
    Board Regular JustynaMK's Avatar
    Join Date
    Aug 2016
    Location
    London, UK
    Posts
    436
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default 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")
    Edit: Sorry @WaterGypsy, I just noticed your answer!
    Last edited by JustynaMK; Jul 22nd, 2019 at 09:55 AM.

  4. #4
    Rules violation
    Join Date
    Jan 2012
    Posts
    912
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate ship day of week without using a date

    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")

  5. #5
    New Member
    Join Date
    May 2016
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate ship day of week without using a date

    Quote Originally Posted by theBardd View Post
    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!!

  6. #6
    New Member
    Join Date
    May 2016
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default 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. #7
    Board Regular Aryatect's Avatar
    Join Date
    Jun 2019
    Location
    Chicago, USA
    Posts
    300
    Post Thanks / Like
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    Default 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")
    Last edited by Aryatect; Jul 22nd, 2019 at 11:00 AM. Reason: adding reference
    Thanks!

    - forum use guidelines, forum rules and terms of use
    - Try searching for your answer first, see how
    - Read the FAQs
    - List of BB codes

  8. #8
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,300
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default 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)

    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

  9. #9
    New Member
    Join Date
    May 2016
    Posts
    12
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Calculate ship day of week without using a date

    Thanks Eric! That worked!!

    Thank you again everyone! You are life savers!

  10. #10
    MrExcel MVP Eric W's Avatar
    Join Date
    Aug 2015
    Location
    Bountiful, UT
    Posts
    8,300
    Post Thanks / Like
    Mentioned
    41 Post(s)
    Tagged
    5 Thread(s)

    Default Re: Calculate ship day of week without using a date

    Glad we could help!
    Cheers,
    Eric

    When you eliminate the impossible, whatever remains, however improbable, must be the truth.

    -Posting guidelines, forum rules, terms of use, FAQs, BB codes, See how to search the forum
    -Post a screen shot with the HTML Maker

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •