Summing Time Periods in a Table Using Structural References

%3CLINGO-SUB%20id%3D%22lingo-sub-2212301%22%20slang%3D%22en-US%22%3ESumming%20Time%20Periods%20in%20a%20Table%20Using%20Structural%20References%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2212301%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20in%20a%20spreadsheet%20where%20I%20enter%20data%20pertaining%20to%20my%20ship%20voyages.%26nbsp%3B%20There%20is%20a%20column%20for%20departure%20day%2Ftime%20and%20another%20for%20arrival%20day%2Ftime.%26nbsp%3B%20I%20am%20trying%20to%20sum%20the%20number%20of%20hours%20and%20minutes%20(hh%3Amm)%20in%20a%20summary%20display%20not%20in%20the%20table%20using%20structural%20references%20so%20I%20can%20add%2C%20delete%20items%20in%20the%20table%20without%20destroying%20the%20formulas.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20added%20two%20columns%20to%20the%20right%20in%20the%20table%20for%20troubleshooting.%20They%20are%20%22Time%22%20and%20%22cc%22.%26nbsp%3B%20They%20will%20be%20deleted%20by%20me%20later.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20can%20get%20the%20correct%20results%20using%26nbsp%3B%3DSUM(DeckLog%5BTime%5D)%20in%20the%20summary%20display%2C%20where%20%3CEM%3ETime%3C%2FEM%3E%20is%20the%20column%20I%20temporarily%20added%20to%20the%20table.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%26nbsp%3B%3CEM%3ETime%3C%2FEM%3E%20column%26nbsp%3B%20I%20use%20the%20formula%26nbsp%3B%3DSUM(%5B%40%5BArrival%20Date%2FTime%5D%5D-%5B%40%5BDeparture%20Date%2FTime%5D%5D)%20and%20it%20calculates%20correctly.%26nbsp%3B%20But%20if%20I%20try%20to%20substitute%20the%26nbsp%3B%3DSUM(%5B%40%5BArrival%20Date%2FTime%5D%5D-%5B%40%5BDeparture%20Date%2FTime%5D%5D)%20formula%20into%20the%20summary%20display%20block%20it%20doesn't%20work.%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20also%20have%20a%20column%20%22Time%20Underway%22%20that%20calculates%20each%20voyage's%20time%20correctly%20but%20doesn't%20work%20when%20I%20try%20to%20sum%20the%20times%20in%20it%20in%20the%20summary%20display.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI've%20attached%20the%20worksheet.%26nbsp%3B%20I%20would%20really%20appreciate%20help%20from%20someone%20in%20making%20this%20work%20and%20to%20understand%20where%20I%20am%20going%20wrong.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ERegards%2C%3C%2FP%3E%3CP%3EGeorge%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2212301%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2212342%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20Time%20Periods%20in%20a%20Table%20Using%20Structural%20References%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2212342%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F990681%22%20target%3D%22_blank%22%3E%40George_Weston%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20formula%20should%20be%3A%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DSUM(DeckLog%5BArrival%20Date%2FTime%5D-DeckLog%5BDeparture%20Date%2FTime%5D)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYour%20values%20in%20%22Time%20Underway%22%20are%20text.%20Text%20will%20be%20ignored%20in%20SUM().%3C%2FP%3E%3CP%3EAnd%20you%20formula%20in%20%22Time%22%20does%20not%20need%20the%20SUM()%20function.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2212568%22%20slang%3D%22en-US%22%3ERe%3A%20Summing%20Time%20Periods%20in%20a%20Table%20Using%20Structural%20References%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2212568%22%20slang%3D%22en-US%22%3EDetlef%2C%3CBR%20%2F%3EThe%20formula%20you%20provided%20looks%20exactly%20the%20same%20as%20one%20I%20was%20using.%3CBR%20%2F%3E%3DSUM(DeckLog%5BArrival%20Date%2FTime%5D-DeckLog%5BDeparture%20Date%2FTime%5D)%3CBR%20%2F%3EIt%20still%20isn't%20working.%3CBR%20%2F%3EAny%20ideas%3F%3CBR%20%2F%3EGeorge%3CBR%20%2F%3E%3CBR%20%2F%3E%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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

 

 

5 Replies

@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.

 

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


@George_Weston 

See attached file. The formula is in C6.

 

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

@George_Weston 

image.png

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