Calculate delivery times and ETAs from decimal time

[adwh78]

Hi all,

Newcomer here so be gentle. I have an issue which I cannot solve and I'm hoping to get some clarity here.

I'm in logistics and need to figure out how to:
1. Assign times for each load that leaves my depot, ie; how long it takes to get from the depot to deliver A, B, C etc.
2. Display delivery ETA's for each delivery point, based on the dispatch time in the system and (up to 4 delivery points per load.)

It would be amazing if I could figure out a way to have it display the time for each delivery point to one and other too. Eg; Depot to delivery 1, delivery 1 to delivery 2, delivery 2 to delivery 3, delivery 3 back to depot. But this may not be possible.

In summary, I want the excel sheet to self populate delivery ETA's for each delivery as soon as the system logs the dispatch time. Only thing is the system enters the dispatch time in as a decimal, eg "6.30". Any clues here?

Any assistance would be greatly appreciated.

[AliGW]

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however the thread title does not really convey what your request is about. Tell us what you are trying to do, not how you think it should be done.

Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).

Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.

(Note: this change is not optional. You are new so I’ve done it for you today.)

[Glenn Kennedy]

And I changed it again... at the same time!!!

[AliGW]

Oops! LOL!

[Glenn Kennedy]

Ok. here is a possible solution, based on 2 helper columns (E & F). It should be possible to dump them... but I haven't got there yet.. and have to leave for a while.

Are you OK with the current approach?



You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

[adwh78]

Hi again,

Sorry for the unclear title... I was pulling my hair out yesterday lol. Thanks for your assistance with this. Noob alert lol.

[adwh78]

Ok. here is a possible solution, based on 2 helper columns (E & F). It should be possible to dump them... but I haven't got there yet.. and have to leave for a while.

Are you OK with the current approach?



You seem to be using an older version of Excel than me. So, please refer to the attached file. If the formulae are enclosed within a pair of { }, these are array formulae.

These are a little different from ordinary formulae in that they MUST be confirmed in the FIRST CELL ONLY by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. After that, the array can be dragged down as normal, to cover the desired range.

You will know the array is active when you see the curly brackets { } appear around the outside of your formula. If you do not use CTRL+SHIFT+ENTER you will (almost always) get an error message or an incorrect answer. Press F2 on that cell and try again.

Don't type the curly brackets yourself - it won't work...

Hey Glen,

That looks like what I'm, chasing. Thats's awesome. I really appreciate your knowledge and help.

[Glenn Kennedy]

You're welcome.

---

It would be very nice if you were to just click the Add Reputation button at the foot of any of the posts of members who helped you reach a solution.

Finally, if that takes care of your original question, please select "Thread Tools" from the menu link above and mark this thread as SOLVED.