Mar 15 2021 04:07 PM
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
Mar 15 2021 04:49 PM
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.
Mar 15 2021 07:37 PM
Mar 15 2021 10:33 PM
Mar 15 2021 11:59 PM
Mar 16 2021 03:15 AM
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