PDA

View Full Version : Time Calculation



Macrosian
07-01-2012, 10:49 PM
I need to calculate time for Multiple destination using excel, Please help

Input:

I have an excel sheet with 6 Colums (5 represents travel spots and last 1 represents total time)

Eg: If I select the colums as (A)Frankfurt-->Indonesia-->USA-->Russia-->Japan (B)

It should calculate the total travel time from A --> B as 120 Minutes and give the result in 6th Colum as 120 minutes

Pre-set time :
Frankfurt-->Indonesia = 20 Minutes
Indonesia-->USA = 20 Minutes
USA-->Russia = 40 minutes
Russia-->Japan = 40 minutes

Bob Phillips
07-02-2012, 12:28 AM
Can you post an example workbook, and explain what you mean by '... If I select the colums as ...'.

Macrosian
07-02-2012, 12:36 AM
Example attached

But don't know how to set specific time for the travel

Aussiebear
07-02-2012, 12:52 AM
Better resend a workbook with correct data. Just having a workbook with countries and no time allocations is making things hard, even for Bob.

Macrosian
07-02-2012, 01:09 AM
You may put any time as its a rough example only

Bob Phillips
07-02-2012, 01:19 AM
I also asked you to explain what you mean by '... If I select the colums as ...'. We need to know how you want to trigger the calculation.

Macrosian
07-02-2012, 01:21 AM
If i selected the 5 Colums (different countries) then I will get the total time

else
I selected only 2 counties (2 colums) i will get only the travel time between the selected

Bob Phillips
07-02-2012, 01:26 AM
This is guessing a little bit, because I am assuming that there is nothing in A3, and that B3 covers the time from America to Greece.

Add this code to the worksheet code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Me.Range("A2:E2")) Is Nothing Then

Me.Range("F2").Value = Application.Sum(Target.Offset(1, 1).Resize(1, Target.Columns.Count - 1))
End If
End Sub

Macrosian
07-02-2012, 02:20 AM
Where i will put the preset time for each countries combination

USA-Japan = 20 Minute
USA - Russia = 30 Minute
Japan - Russia = 40 minutes

etc

Bob Phillips
07-02-2012, 03:16 AM
In row 3, US-Japan time under Japan, etc.

Macrosian
07-02-2012, 03:20 AM
Not working, See attached file

Bob Phillips
07-02-2012, 04:06 AM
Did you read what I said in post #8.

If you have 5 places, you only have 4 distances, so you cannot have 5 times.

Macrosian
07-02-2012, 09:27 PM
I removed the time value from A3, But still getting errors. Is there any other calculation i can put with the help of a calculate button somewhere in the sheet

Bob Phillips
07-03-2012, 12:49 AM
A button won't make any difference, it works fine for me as I understand it, so it seems to be a difference in expectation. Explain what 'errors' you are getting.