Forum Discussion

George_Weston's avatar
George_Weston
Copper Contributor
Mar 15, 2021

Summing Time Periods in a Table Using Structural References

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

 

 

excel
Formulas and Functions

5 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Mar 16, 2021

    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

  • chahine's avatar
    chahine
    Iron Contributor
    Mar 16, 2021
    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
  • Detlef_Lewin's avatar
    Detlef_Lewin
    Silver Contributor
    Mar 15, 2021

    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.

     

Resources