# Trouble with a formula in Sheets to forecast shipment arrival

I have big trouble making my formula works and I am pretty sure that this is simple. So simple that I am litteraly struggling on it for weeks now.. So I give up :'(

Let me explain: I am using sheets to schedule when my shipment ETA. So I have these informations:

• "CMD": which is when the customer made his command

• "PM": which is just an opt-in to know if the command were made after noon

• "SHIPMENT": which is just a choice of tranporters

• "WORKDAYS": which is the number of workdays for each transporter. linked with "shipment"

• "ETA": which is the Formula I want to make to have the accurate estimated time of arrival

(see image below where I show the correct results in ETA)

Here you can find the Sheet

Things to know:

• workday delivery is each day except on sunday
• if "PM"=true, then you can add a day to delivery (see raw 2 and 3 on the example)
• if "CMD" is: friday+"PM"=true then the count starts on next monday (see raw 6 and 7 on the example)
• if "CMD" is: saturday or sunday then the count starts on next monday (see raw 8 and 9 on the example)

Things I've tried so far:

• a lot of formulas to say different things. I tried the barbarian way (with holidays and different workdays in sheet 'data v2'):

=if(isblank(B7);""; if(C7=true;ifs(G7="Mondial Relay";WORKDAY.INTL(B7;H7+1;'data v2'!\$K\$4;'data v2'!\$M\$3:\$M\$13);G7="Chrono 24h";workday.intl(B7;H7+1;'data v2'!\$K\$3;'data v2'!\$M\$3:\$M\$13);G7="Colissimo";workday.intl(B7;H7+1;'data v2'!\$K\$5;'data v2'!\$M\$3:\$M\$13));ifs(G7="Mondial Relay";WORKDAY.INTL(B7;H7;'data v2'!\$K\$4;'data v2'!\$M\$3:\$M\$13);G7="Chrono 24h";workday.intl(B7;H7;'data v2'!\$K\$3;'data v2'!\$M\$3:\$M\$13);G7="Colissimo";workday.intl(B7;H7;'data v2'!\$K\$5;'data v2'!\$M\$3:\$M\$13))))

And I tried the "I"m so smart" way (with just "CMD"+"workdays" and when friday+PM or saturday or sunday = next monday + "workdays"):

=if(isblank(B2);"";IFS(C2=true;WORKDAY.INTL(B2+1;H2;11);weekday(B2)=7;WORKDAY.INTL(B2+2;H2;11);weekday(B2)=1;WORKDAY.INTL(B2+1;H2;11);and(weekday(B2)>1;weekday(B2)<7);WORKDAY.INTL(B2;H2;11);and(C2=true;weekday=6);WORKDAY.INTL(B2+1;H2;1)))

And tried to find some way to turn theses days into next monday

=IF(WEEKDAY(B50;3)=0;B50;1)

=B51+MOD(9-B51;7)

=B2+MOD(9-WEEKDAY(B2);7)

And finally thought "hey, let's make it simple !" Guess what.. did not work

=WORKDAY.INTL(B49;H49;11)

Do someone has a solution before I start crying ?

Many thanks if you read this

• Welcome! Don't cry - no formula is worth tears. Moreover, the task is not as difficult as it might seem. I would be grateful if you could create a table with test data, with the "SHIPMENT/WORKDAYS" table and with the table shown in the screenshot, and add a link to this spreadsheet in the question text: the most boring part of preparing the answer is to manually retype the test data from their photo... Commented Apr 14, 2023 at 13:40
• Hey JohnSUN, Thanks for your message ! You're right, I forgot to post the link of it. I edited my post docs.google.com/spreadsheets/d/… Commented Apr 14, 2023 at 13:47
• While links to external resources might be helpful, questions on this site should be self contained. Show what you have tried including sample data , what you found from this site, from the official resources for end-users and why they didn't meet your needs. Please edit your question to provide the requested details directly into the question body. If you need further help, please read first Guides to ask a [google-sheets] question. Commented Apr 14, 2023 at 15:08
• I think you have misunderstood the purpose of WORKDAYS in the context of Sheets. Whether you might work on a Samedi or Dimanche is irrelevant in this case. That's what the OP has selected to perform. Commented Apr 15, 2023 at 15:15

### Formula

The core of the formula is `=WORKDAY.INTL(startdate; days; 11)`

`11` means Sunday is the only non-work day

``````=WORKDAY.INTL(A2; D2+
IFS(OR(WEEKDAY(A2)=1; WEEKDAY(A2)=7); 1;
AND(WEEKDAY(A2)=6; B2); 2;
B2; 1; TRUE; 0);
11)
``````
Weekday             Offset Formula
AM - Sunday
PM - Sunday
AM - Saturday
PM - Saturday
1 Count from Monday
`OR(WEEKDAY(A2)=1; WEEKDAY(A2)=7); 1`
Sunday is always skipped, offsetting Sat and Sun by 1 has the same effect.
PM - Friday 2 Count from Monday
`AND(WEEKDAY(A2)=6; B2); 2`
PM - Monday
PM - Tuesday
PM - Wednesday
PM - Thursday
`B2; 1`
AM - Monday
AM - Tuesday
AM - Wednesday
AM - Thursday
AM - Friday
0 No Offset
`TRUE; 0`

### Single-Cell Solution

The formula above is adapted by mapping the values into a LAMBDA function so that a single formula in `E2` will populate all the rows.

``````=MAP(A2:A; B2:B; D2:D; LAMBDA(a; b; d; IF(a<>"";
WORKDAY.INTL(a; d+
IFS(OR(WEEKDAY(a)=1; WEEKDAY(a)=7); 1;
AND(WEEKDAY(a)=6; b); 2;
b; 1; TRUE; 0);
11); IFERROR(1/0))))
``````