Calculation on days of the week

[Max Alejandro Molina]

Hello Excel Gurus, I have been trying to develop an approach to try to get the cicle time of stores which implicates the days a store generates product orders and the days the products are delivered.I have calculated it manually in yellow columns. This can be achieved manually by doing the following 3 simple steps; first we have to get something called revision date:

1. Revision Time: a store may have several generation days and we have to manually test for the longest time between all generations for instance if a store generates in MONDAYS and THURSDAYS then we can say that from MO to TU there are 3 days, but from TU to MO there are 4 days so we get the 4 (the max) and that will be the revision date, sometimes the stores only have 1 generation date which means to always be 7 days of revision.

2.Lead Time: taking the previous example now lets say this store delivers the product on WEDNESDAYS and SUNDAYS, now we will compare the time between MONDAYS-WEDNESDAYS (Generation vs Delivery) which is 2 days and then the time between THURSDAYS and SUNDAYS (Generation vs Delivery) which is 3 days, and then we will get the biggest which is 3 days.

3. At this point we have Revision Date: 4 days and Lead Time: 3 days, now we simply sum them up and we get a cicle time of 7.


Stuff to consider:

*the number of generation days will always match the number of deliveries.
*store may generate even 5 days
*Macros can be used

Thank you for the help, and hopefully this can be achieved with formulas, if you have any questions let me know!

 

[Max Alejandro Molina]

@Faseeh @Somendra Misra @NARAYANK991 Can any of you guys help me please??

 

[Faseeh]

HI max

Can not understand the days name, can you explain that step once again. and days name in English.

 

[Faseeh]

in Cell R5 & S5, Fri-Tues means 5 days while you have 6 in cell, Thues to Friday are 4 days while you have 3 next to it?? Plz Explain.

 

[Max Alejandro Molina]

Hi Faseeh, the days are in english but i put only the first two characters of the whole word (e.i. MONDAY = MO)..

The numbers from D:5 to Q:5 are just are just the same days from B5:C5 but written in coordinates ..

R5 is 4 because from in the case 1 the store generates purchase order from TU to FR which is 3 days in between those 2 days (TUFR) and now we comparethe days from FR to TU which are 4 days and we get the biggest number which is 4 days.

Now in S5 we compare the distance from the first generation to the first delivery... and the second generation to the second delivery and we realized that from TU to FR there are 3 and from FR to TU there are 4 days so choose the max number which is 4 days also. and the same patter has to be followed even when there are more than 2 generations per store, and thats the hard part.

 

[Faseeh]

Hi Max,

I have picked just one entry that was the longest one and am working over it. please see if results are correct. Sheet HOja1(2)

 

[Max Alejandro Molina]

Hi Faseeh, the yellow columns are the right answers I did manually and I was looking to have a formula that would give me the output in column R and S (Sheet called "Hoja 1"), so I could just sum them up and get the Cicle Time (CT), besides your example in Hoja1 (2) in A6:C6 is wrong (is not the case 4)

 

[Faseeh]

Ok will try it tomorrow morning.

BTW your question is still not clear...

Can you explain the differene between mine calculated values and yours highlighted in Red Fill.

 

[Max Alejandro Molina]

Thank you Faseeh!

 

[Max Alejandro Molina]

Ok will try it tomorrow morning.

BTW your question is still not clear...

Can you explain the differene between mine calculated values and yours highlighted in Red Fill.

Hi Faseeh!

In order to get the revision time (RT) we do not take into account the deliveries, we just compare the distances between generations which is from column D to J.

RT manual calculation:

Generation Days: SUWE

From SU to WE there are 3 days in between, but the calculation does not end here. Now we have to count from 2nd generation (WE) to the other generation (SU) which there are 4 days in between. So we choose 4 days because is bigger than the first comparison (3 days).

Now it becomes kind of tricky when there are 3 generation so here is an example:

Generation Days: MOWEFR

The comparison of distances between generations has to be in the following order;

Consecutively

Distance from MO to WE : 2 days
Distance from WE to FR : 2 days
Distance from FR to MO : 3 days

So we choose 3 days which is the longest (max)

The logic I think it would help is to compare each generation thats is only consecutive to the right and when the generation is SA to compare it to the closest generation from SUN to front.

Let me know if you have any questions Faseeh and again thank you so uch for the effort!

 

[Faseeh]

HI Max

Please see attachment. i have tried to give diff. of days in E5:K16. The only difference in your and my result (col S) is in cell with longest generations. can you check this.

 

[Faseeh]

when the generation is SA to compare it to the closest generation from SUN to front.

Ok for the Generation in cell B8 [SUMOTUWETHFRSA] the last day is SA so i need to compare it with MO [SUMOTUWETHFRSA], which is guess is the closed day after Sunday? had there been no monday i had to compare it to TU [SUTUWETHFRSA] that is next in the generation, Is this concept right?

 

[Max Alejandro Molina]

Faseeh my apologies! the right input in B8 is [SUMOTUWETHFR] which gives us an ouput of 2 in cell J8. If any store generates each day of the week its revision time is automatically 1 therefore if it generates just once a week its revision time will automatically be 7, so I already added this logic tests to your original formula and it all seems to be working perfectly. In regard to the delivery lead time from column U, I came out with a solution. So it is solved!!!

Thank you so much for the help man, you rock!!