GoogleSheets: Count number of days between days listed in text

Hi
Seeking some guidance in excel formulas if there is a way to calculate number of days between days listed as text with no dates (Monday, Tuesday etc)

Attached sample output with desired result seeking to achieve

Any help/guidance much appreciated!

Thanks in advance!

Can the whole process ever take more than 2 weeks?
- No

if an individual row takes more than 1 week, will column B or C ALWAYS contain a weekend day?
- No will not always contain a weekend though may travel over the weekend. Example: Train Departure Day = Friday, Train Arrival Day = Monday

If col A is Monday and Col B is also Monday, how can you tell if it is the SAME Monday or the NEXT Monday?
- if the day is listed the same in these two col, assume = same day

If you change the format of the days in dddd and create an easy input validation of these days the formulas can be shortened to:

Lead days incl. weekend:

Formula:

Please Login or Register  to view this content.

Lead days excl. weekend:

Formula:

Please Login or Register  to view this content.

Is there a way this can also be done in google sheets is this formula unique to excel only? I tried replicating in google sheets though gives me an error when formulating lead days - 'Function MINUS parameter 1 expects number values. But 'Wednesday' is a text and cannot be coerced to a number'

Hi Glenn,
For some reason the formula is not picking up the correct days for some examples as below, it should return 8 days (inc WE) and 6 days (ex WE), formula returns 1 day. Is it a easy fix for this i could be missing
Collection Day Train Departure Day Train Arrival Day Delivery Day
Friday Monday Friday Friday

Attachment 796797

HI HansDouwe
this perfectly does the job, it just seems the formula is not user friendly when using it with google sheets

Thanks for the feedback and rep. .
I have also learned from looking for the right answer.

I have no idea about google sheets.
It is best to post this question on the forum "For other Platforms".

Hi Glenn
Your formulas work with google sheets as well, just seems some days are not lining up with that example i provided earlier. Could you explain the formula in helper 5 is trying to achieve?

Thanks

Hi HansDouwe
I have a scenario where all days are the same and the formula is reading it as same day for everything returning 1 day lead time. I am wanting the formula to read this as following week and to return lead days as 8 (inc weekend) & 6 (excluding weekend) on sample example below. Is it possible?

Example:
Collection Day - Thursday
Train Departure Day - Thursday (same day as collection)
Train Arrival Day - Thursday (arrives following Thursday)
Delivery Day - Thursday (same day as train arrival day)