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.
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.