Calculating total flight time plus connection time

[cdchapman]

Hello,

I'm hoping someone may be able to help me with a final piece on my spreadsheet that I just cannot get to work.

On my spreadsheet I have a column (lets say C) which contains the total flight time based on departure and arrival times (in cols A and B) for a number of different routes (one route per row). As some flights are international, the total flight time could in some cases be over 24 hours, so I have formatted the total time as [h]:mm. An added complication is that some routes involve just one flight, and others two flights (which also have a connection time stored in another column that needs to be taken into account.

I have a further column which stores the total time for each route - this is the sum of the flight time for flight 1 plus, if there is a second flight, the connection time and the flight time for flight 2. Again, as this total time is likely to be over 24 hours for some routes I have formatted this column as [h]:mm. However for total times of less than 24 hours, this format doesn't seem to be working.

To illustrate:

Cols A B C D
Rows Flight Time 1 Flight Time 2 Connection Time Total Time
1 1:15 10:05 1:45 13:05 correct
2 1:17 12:50 2:55 41:02 incorrect (should be 17:02)
3 21:20 6:55 2:20 30:25 correct

All times have been formatted as [h]:mm. If I format the Total Time column as h:mm, then although rows 2 and 3 show the correct total time, the total time in row 3 above shows as 6:25 which is incorrect. I just cannot seem to get a format that consistently shows the correct total time in all scenarios.

Just for reference the formula in column D is A1+B1+C1. I've also tried SUM(A1,B1,C1), but that hasn't worked either.

Any help would be appreciated.

Thanks
Chris

 

[ParamRay]

Chris,

Are you absolutely certain that all your cells have the number format [h]:mm ?

I only ask because I have just replicated your table in Excel and it has given me the correct results.

Cheers,
Greg

 

[cdchapman]

Greg,

I have double checked the formatting and it is definately [h]:mm. It's a pity I can't post a sample of my worksheet with the formulas and formatting, then you would be able to see what I have.

Chris

 

[West Man]

Try copying the 3 cells in question and pasting values into blank cells. Now format 3 other blank cells as [h]:mm and key the times into these. Copy and paste values for these keyed cells. Compare the values to see where the difference is.

 

[cdchapman]

Hi West Man,

Copying and pasting cells as values I get
0.545139
1.709722
1.267361

Formatting blank cells as [h]:mm and key above times manually:
13:05
41:02 (still incorrect)
30:25

Copying these three cells and pasting as values:
0.545139
1.709722
1.267361

So as you can see there is no difference in the values, but interestingly the cells pre-formatted as [h]:mm show the following in the formula bar:

13:05:00
01/01/1900 17:02:00
01/01/1900 06:25:00

Not sure why Excel has added the date part to the last two rows and not the first row?

 

[West Man]

Out of town this weekend so just now responding.

It looks like you have a data problem. Those cells where the values (from pasting values) are greater than 1 are times in excess of 24 hours regardless of what is displayed

 

[cdchapman]

West Man,

Just got round to looking at this again, and I seem to have solved it by wrapping each time (Flight Time 1, Flight Time 2 and Connection Time) in MOD(...,1) before totalling them up (the total time formatted as [h]:mm). Not quite sure why this seems to work though?

Anyway, thanks for your help and advice.

Chris

 

[West Man]

Mod(...,1) removes any time in excess of 24 hours prior to summing