Summing Time Periods in a Table Using Structural References

[George_Weston]

I have a table in a spreadsheet where I enter data pertaining to my ship voyages.  There is a column for departure day/time and another for arrival day/time.  I am trying to sum the number of hours and minutes (hh:mm) in a summary display not in the table using structural references so I can add, delete items in the table without destroying the formulas.

I added two columns to the right in the table for troubleshooting. They are "Time" and "cc".  They will be deleted by me later.

I can get the correct results using =SUM(DeckLog[Time]) in the summary display, where Time is the column I temporarily added to the table. 

In the Time column  I use the formula =SUM([@[Arrival Date/Time]]-[@[Departure Date/Time]]) and it calculates correctly.  But if I try to substitute the =SUM([@[Arrival Date/Time]]-[@[Departure Date/Time]]) formula into the summary display block it doesn't work.  

I also have a column "Time Underway" that calculates each voyage's time correctly but doesn't work when I try to sum the times in it in the summary display.

I've attached the worksheet.  I would really appreciate help from someone in making this work and to understand where I am going wrong.

Regards,

George

[#2]

@George_Weston 

Your formula should be:

=SUM(DeckLog[Arrival Date/Time]-DeckLog[Departure Date/Time])

Your values in "Time Underway" are text. Text will be ignored in SUM().

And you formula in "Time" does not need the SUM() function.

[#3]

Detlef,
The formula you provided looks exactly the same as one I was using.
=SUM(DeckLog[Arrival Date/Time]-DeckLog[Departure Date/Time])
It still isn't working.
Any ideas?
George

[#4]

@George_Weston 

See attached file. The formula is in C6.

[#5]

my advise so that it will work, whenever you have dates/time , dont make it text, change the format to date (right aligned should be) & then do any operations that you want, it will work

issue in your case is that you have your dates as text, so sum will give an error

[#6]

@George_Weston 

The image shows some formulae (red) that do work for the reasons explained by others.  The number formatting required to show the time underway is not straightforward, and uses

d "days," h "hours, "
m "minutes" 

where the linefeed is inserted using Ctrl/J