Formula Help

A

adem

Board Regular
Joined
Mar 3, 2003
Messages
87
Example Spreadsheet:

Cell F1 is a variable day, typed in by the user (the day a product has been ordered) e.g. SATURDAY
Column A is a list of static suppliers e.g. TESCO, SAINSBURY'S etc
Columns B, C & D indicate static delivery days for each store e.g B1 = MONDAY C1 = TUESDAY D1 = THURSDAY

I would like to put a formula into column E that looks at the 'order day' in F1, and returns the number of days until the next supplier delivery is due.

Any help would be fantastic

Thanks!
 
Sort by date Sort by votes
Hi Adem,

Here's an improvement over what I posted earlier (or at least, a simplification):
MrE3084.xls
ABCDEFG
1SupplierDel_Dt1Del_Dt2Del_Dt3DaystilDel(i)Ord_DtDaystilDel(ii)
2TescoMondayTuesdayFriday2Wednesday2
3Sainsbury'sTuesdayThursdayFriday3Saturday3
4OmnicomWednesdayThursdayFriday0Wednesday1
5SyscoMondayWednesdayThursday0Wednesday1
6Shallot'sTuesdayWednesdayWednesday4Friday4
7FeddersonMondayMondayMonday0Monday7
8(Sameday(Sameday
9List:deliveriesdeliveries
10Saturdayallowed)notallowed)
11Sunday
12Monday
13Tuesday
14Wednesday
15Thursday
16Friday
Sheet2


The array formula in cell E2 is now:

=MIN(MOD(MATCH(B2:D2,List,0)-MATCH(F2,List,0),7))

where List is defined as the range A10:A16. Note that this formula allows for same day deliveries. If same day deliveries are not allowed (or are not possible), then the array formula becomes (cell G2):

=MIN(7+MOD(MATCH(B2:D2,List,0)-MATCH(F2,List,0),-7))

Finally, the same caveats concerning supplier deliveries exist as before (that is, no blank 2nd or 3rd delivery dates). And, you could create a defined name for List instead of using a range reference. It would be:

={"Saturday";"Sunday";"Monday";"Tuesday";"Wednesday";"Thursday";"Friday"}

--Tom
 
Upvote 0

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Hi Adem:

In my earlier post, as result I had posted the next delivery day rather than the number of days to the next delivery day. I have also updated my updated and still convoluted formula to cover the case of 0 days between the posted day and the same day delivery ...
y040308h1.xls
ABCDEFGHI
1TESCOMondayTuesdayFriday2Wednesdaysource
2SAINSBURY'STuesdayThursdayFriday3Saturdayresults
3Bobby'sMondayTuesdayFriday1Thursday
4Gloria'sWednesdayThursdayFriday1Tuesday
5WednesdayThursdayFriday0Wednesday
6MondayWednesdayThursday0Wednesday
7TuesdayWednesdayWednesday4Friday
8MondayMondayMonday0Monday
9
10
11INTERMEDIARYWORKINGTABLE
12TESCO23624Sunday1
13SAINSBURY'S35637Monday2
14Bobby's23615Tuesday3
15Gloria's45613Wednesday4
1645604Thursday5
1724504Friday6
1834446Saturday7
1922202
Sheet4


Array Formula in cell E12 is ... =MOD(INDEX(B12:D12,IF(SUM(--(IF(B12:D12>F12,1,0)))=0,1,IF(ISNA(MATCH(F12,B12:D12,0)),IF(ISNA(MATCH(F12,B12:D12)),1,MATCH(F12,B12:D12)+1),MATCH(F12,B12:D12,0))))-F12,7)
 
Upvote 0
MrExcel.xls
ABCDEFG
1SupplierName1stDelivery2nddelivery3rddeliveryDaysuntilDeliveryUserOrderDate
2TescoMondayTuesdayFriday2.00Wednesday
3Sainsbury'sTuesdayThursdayFriday3.00Saturday
4OmnicomWednesdayThursdayFriday0.00Wednesday
5SyscoMondayWednesdayThursday0.00Wednesday
6Shallot'sTuesdayWednesdayWednesday4.00Friday
7FeddersonMondayMondayMonday6.00Tuesday
8
9MyList
10Sunday
11Monday
12Tuesday
13Wednesday
14Thursday
15Friday
16Saturday
17
Sheet1


In Cells A10, A11…... to A16 enter :

1/01/1900, 2/01/1900...… to 7/01/1900 , then custom format to "dddd"


Highlight the range A10 to A16

Goto the Insert menu and select Name, then Define.

Type in the name "MyList" under "Names in Workbook".


Highlight the range of Cells B2:D7 and F2:F7

Goto Data >> Validation>> allows: choose "List" >> sources: enter "=MyList"


E2 enter :

=MIN(IF(B2-F2>=0,B2-F2,7-F2+B2),IF(C2-F2>=0,C2-F2,7-F2+C2),IF(D2-F2>=0,D2-F2,7-F2+D2))

and copied the formula drag dowm to E2


All dates will then be selected from the validation dropdrown list


Hope that helps
 
Upvote 0
So many ways to skin this cat! Well, such is the utility of this forum. :)

Originally posted by bosco_yip:
=MIN(IF(B2-F2>=0,B2-F2,7-F2+B2),IF(C2-F2>=0,C2-F2,7-F2+C2),IF(D2-F2>=0,D2-F2,7-F2+D2))
Click to expand...
can be shortened to the array formula:

=MIN(MOD(B2:D2-F2,7))

if you're so inclined. Otherwise, nice post.

--Tom
 
Upvote 0
Very Nice contributions from bosco_yip and Tom.

In the following illustration ...
Book1
ABCDEF
1SupplierDelivery1Delivery2Delivery3DaysToDeliverydelivery
2TescoMondayTuesdayFriday2Wednesday
3Sainsbury'sTuesdayThursdayFriday3Saturday
4OmnicomWednesdayThursdayFriday0Wednesday
5SyscoMondayWednesdayThursday0Wednesday
6Shallot'sTuesdayWednesdayWednesday4Friday
7FeddersonMondayMondayMonday6Tuesday
Sheet2 (2)


Using Insert|name|Define, I have asigned values to each of the weekdays Sunday through saturday from 1 to 7, then use the formula {=MIN(MOD(B2:D2-F2,7))} for daysToDelivery.
 
Upvote 0
Hey, I just stumbled over this post again, and saw a whole host more opportunities! Thanks a lot.

I'm especially impressed with the modifications to allow for a "0" days delivery. I will go and have another play with these now and see which one works best for this case.

Do any of the newer examples allow for blank delivery days? i.e. Only two of the three delivery day opportunities used up?

I'll have a tinkle and see what I can find, thanks again...
 
Upvote 0
adem said:
...Do any of the newer examples allow for blank delivery days? i.e. Only two of the three delivery day opportunities used up?...
Click to expand...

What follows does so...
adem.xls
ABCDEFGHIJ
1SupplierDeliveryDaysDaysToDeliveryDayOforderTable
2TescoMondayTuesdayFriday2WednesdayFriday6
3DrescMonday4ThursdayMonday2
4NowaskTuesdayFriday2WednesdaySaturday7
5VdoskTuesdayThursday1MondaySunday1
6WertoFriday4MondayThursday5
7TrentMonday0MondayTuesday3
8xnasMondayFriday3TuesdayWednesday4
9CarhaTuesday3Saturday
10
Sheet1


Table, a defined name, refers to I2:J8.

The formula in E2 is:

=MIN(MOD(LOOKUP($B2:INDEX($B2:$D2,MATCH(REPT("z",255),$B2:$D2)),Table)-LOOKUP(F2,Table),7))

which must be confirmed with control+shift+enter instead of just with enter.

Adem: The foregoing puts together the contribs of others. So, if this satisfies the requirements/specs, watch out when you intend to issue thanks.
 
Upvote 0
Nice one Aladin!

Thanks to every single person who had a hand in this one. The response was overwhelming, there's some clever people on this board, and thankfully some very helpful people too (y)
 
Upvote 0
Hi Adem:

Here is an upgrade to my earlier post of Wed 10 Mar 2004 14 01 to allow for some blank delivery days ...
y040310h1.xls
ABCDEF
1SupplierDelivery1Delivery2Delivery3DaysToDeliverydelivery
2TescoTuesdayFriday2Wednesday
3Sainsbury'sTuesdayThursdayFriday3Saturday
4OmnicomWednesdayThursdayFriday0Wednesday
5SyscoMonday5Wednesday
6Shallot'sWednesdayWednesday5Friday
7FeddersonMondayMondayMonday6Tuesday
Sheet2 (2)


Formula in cell E2 is ... =MIN(IF(B2:D2>0,(MOD(B2:D2-F2,7))))

this is an array formula and is to entered with CTRL+SHIFT+ENTER. This formula is then copied to cells E3:E7
 
Upvote 0
Hi Yogi, simple and clear upgrade contribution.

A bit hightlighted that, in the column F2:F7 for the User Order Date,

You can enter any date and custom format to "dddd", the formula will give the correct result.

Regards
 
Upvote 0
You must log in or register to reply here.

Similar threads

E
  • Solved
Unexpected flaw in WORKDAY formula to calculate date
Replies
7
Views
344
KRice
K
J
  • Question
Formula for weekdays of date in another cell
Replies
4
Views
470
Jaxi
J
M
  • Question
Sum Values Above Based on Adjacent Value
Replies
3
Views
271
Peter_SSs
Peter_SSs
R
  • Question
Sum & If function possible?
Replies
1
Views
293
etaf
etaf
S
  • Question
count number of unique Mondays in a date column
Replies
4
Views
666
Dave Patton
D

Forum statistics

Threads
1,214,962
Messages
6,122,482
Members
449,088
Latest member
Melvetica

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top