• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Please help me get the formulas to find out my desired shipping date. [SOLVED]

R

ryan4646

New Member
i am basically a clerk who handle imports of sea shipping. I have tons of sea shipment schedule to maintain. My boss always Always keeps me in pressure. So many times get wasted to find the shipping only. I have a strong believe that excel masters in here can help me out in this case. Hope I will not be disappointed.

Here is the dates. IF any delivery in china happens on and before wednessday, it will enter in the port at wednessday and shipout at next saturday. No matter what day the delivery date is, it will enter ( Port closing date) at wednessday and it will ship out at next Saturday (ETD). That means if any delivery take place in thurday, it will enter in port next wednessday and will ship in next Saturday. So my key dates are Wednesday and Saturday.


In the excel, there will be three columns. 1> delivery date (any date), 2> Closing date ( next or immediate Wednesday of delivery date) 3> ETD (next Saturday of closing date.i.e Wednesday)


Please help me. I have hongkong, and some other countries also. But if u can help me in this case, I can be able to solve those. Waiting for helping hand.
 
Hi Ryan ,


If I understand you correctly , what you want is :


Given a date in a cell , you want two other cells to be filled in with the nearest Wednesday in the future , and the nearest Saturday in the future.


For example today is June 6 , which is a Thursday ; the nearest Wednesday in the future is the Wednesday next week i.e. June 12. The nearest Saturday in the future is either June 8 or June 15 , but since you want the Saturday after the nearest Wednesday , the correct Saturday will be June 15.


But suppose the delivery date were June 12 , which is a Wednesday itself ; then the two output dates would be June 12 and June 15.


Is this correct ?


Narayan
 
hi narayan, you are super correct. the line you mentioned either june 8 or June 15, it should be June 15 as you already wrote. i am amazed that you understood the whole shipping system within very few time. so can you help me out ?
 
Hi Ryan,


Say your delivery date is in cell A2, Formula for closing date would be: =IF(WEEKDAY(A2,2)>3,A2+10-WEEKDAY(A2,2),IF(WEEKDAY(A2,2)<4,A2+3-WEEKDAY(A2,2),""))

and formula for ETD would be: =B2+3


There could be a better way of doing this, but, hope this helps.
 
@ryan


Hi


yes it is better to upload a sample file with some manual data and you will get the accurate result from Ninja's and forum readers


Thanks


SP
 
hi SP and Narayan and others,


okay. i am uploading my sample file. This sheet contains two sections.


one is for rough, to calculate the ship dates separately which i will use if you guys help me on the based of above information.This will be enough to help me out.


But i use the master file in the second sheet, but i know that it will be tough to insert four or five countires ship formula in on column. But u guys are master in excel. anything is possible with you. so i am uploading it for you. please help me.


hi varak, thanks mate. it is working for china. But i have hongkong, taiwan and donguang to be solved with multiple dates.


god bless you all. here is the link : http://www.mediafire.com/download/n1q3d4quuzwk55s/shipping_help.xls


ryan
 
Hi dave, your one is working also.can you please help me for taiwan, hongkong and donguang port also. i just need the three countries. thats it.
 
Hi Ryan


Take a look at this file https://docs.google.com/file/d/0ByiZqUlQMjxhY3cyOUF0RXlXWm8/edit?usp=sharing


I have created a criteria sheet. Basically days run from sunday to saturday, sunday being 1 saturday being 7.


Formulas are in the main data sheet and will automatically fill the closing and ETD cells based on the country name and delivery date


Let me know if this is ok for your needs
 
Hey dave,


how did you do that ? :) thanks mate. thanks a lot. i think u have experience in shipping.


Hope i will be a super hero in our office from today. thanks a lot again.
 
Hi dave, I am having one more problem here. I Want to add one extra column with ETA. The ETA should be added with ETD. Like china has 15 days transit time, hongkong has 14 days transit time, Taiwan has 16 days, and donguang is 15 days transit time. As I am selecting country in your sheet ETD changing automatically, is that possible to insert a ETA right after ETD column and change it also when I chang the country?


Example : if contry is china, then ETA should be ETD+15 (on the based of your sheet), if it is Taiwan, it should be ETD+16, IF HONGKONG, ETD+14. Can you automatically change that ? I mean, is that possible to change it automatically when I change the country also ? thanks in advance.
 
Hi Ryan


Haha no experience in shipping, I work in payroll.


Is this what you are looking for? https://docs.google.com/file/d/0ByiZqUlQMjxhVDFPVkcwYU1FSzQ/edit?usp=sharing
 
yes. this is the file i wanted. thanks again. you are a genius. i am trying to include korea, and Thailand there. hope i will be successful.if i cant, i will knock you.. :) plz dont mind
 
Hi Ryan


The formulas referring to the criteria sheet look at the whole column so no amendments are needed. You can add as many countries as you like to the bottom of the list
 
Yup. it is happening. Lol that this task i am doing this since one year only calcualting in desk calendar or PC calendar. this tricks could save so many times in these days. thanks chandoo and thanks dave. u r from UK ?
 
You must log in or register to reply here.
Back
Top