none
Sum of two Time columns

    Question

  • There are two calculated columns that display times of two date columns.  
    I want to sum those two "time" columns. Any suggestion is greatly appreciated.

    Example:  
    column 1: 16:35
    column 2: 14:00

    So, result should be 30:35.
    Friday, March 24, 2017 9:24 AM

Answers

  • 1st, I separated hours and minutes from columns into 4 columns.  
    example: column 1 is 12:10 and with
    =Left([column 1],2)

    I get hours and with formula
    =Right(Left([column 1],5),2)

    I get  minutes.  
    2nd, I summed hours and than multiplied them with 60, to get value minutes and summed this value with sum of minutes' columns.  
    3rd, after this I had one column that represented time in minutes. I divided it with 60 in one column, output calculated column type is number with 0 decimals, by this I get whole hours, in another calculated column I used formula
    =MOD([minute column],60)

    . this was for minutes. Again number type with 0 decimals is output calculated column.  
    4th combined column of hours with column of minutes with Concatenate.  

        =CONCATENATE(TEXT(hours,"0"),":",TEXT(minutes,"0"))



    I had to use TEXT because my data was in number with 0 decimals and Concatenate formula would not work if I left data like that. So I had to convert it to text.
    • Marked as answer by DannyNS Monday, April 10, 2017 9:23 AM
    Monday, April 10, 2017 9:22 AM

All replies

  • Add Another calculated Column

    [Column2]+[Column1]=


    • Edited by Stark365 Friday, March 31, 2017 12:10 PM
    Friday, March 31, 2017 12:09 PM
  • Since there is no way to control what order the columns are calculated in I don't think you can just calculate based on the values in the calculated columns.  I think you'll have to calculate the column value using the original formulas that were used to calculate the other columns.

    Paul Stork SharePoint Server MVP
    Principal Architect: Blue Chip Consulting Group
    Blog: http://dontpapanic.com/blog
    Twitter: Follow @pstork
    Please remember to mark your question as "answered" if this solves your problem.

    Friday, March 31, 2017 12:26 PM
  • Hi

    you can't use simple SUM formula for datetime columns. And why to create 2 new separate columns to get hours and minute ? you can calculate the result directly

    Suppose that your columns ( datetime  ) are: COL1 and COL2

    The needed formula will be

    =(HOUR(COL1)+HOUR(COL2)+INT((MINUTE(COL1)+MINUTE(COL2))/60))&":"&(IF(MINUTE(COL1)+MINUTE(COL2)-60*INT((MINUTE(COL1)+MINUTE(COL2))/60)<10;"0"&(MINUTE(COL1)+MINUTE(COL2)-60*INT((MINUTE(COL1)+MINUTE(COL2))/60));MINUTE(COL1)+MINUTE(COL2)-60*INT((MINUTE(COL1)+MINUTE(COL2))/60)))
    Based on your regional settings , maybe it is necessary to replace ";" with ","


    Romeo Donca, Orange Romania (MCSE, MCITP, CCNA) Please Mark As Answer if my post solves your problem or Vote As Helpful if the post has been helpful for you.


    Friday, March 31, 2017 8:52 PM
  • 1st, I separated hours and minutes from columns into 4 columns.  
    example: column 1 is 12:10 and with
    =Left([column 1],2)

    I get hours and with formula
    =Right(Left([column 1],5),2)

    I get  minutes.  
    2nd, I summed hours and than multiplied them with 60, to get value minutes and summed this value with sum of minutes' columns.  
    3rd, after this I had one column that represented time in minutes. I divided it with 60 in one column, output calculated column type is number with 0 decimals, by this I get whole hours, in another calculated column I used formula
    =MOD([minute column],60)

    . this was for minutes. Again number type with 0 decimals is output calculated column.  
    4th combined column of hours with column of minutes with Concatenate.  

        =CONCATENATE(TEXT(hours,"0"),":",TEXT(minutes,"0"))



    I had to use TEXT because my data was in number with 0 decimals and Concatenate formula would not work if I left data like that. So I had to convert it to text.
    • Marked as answer by DannyNS Monday, April 10, 2017 9:23 AM
    Monday, April 10, 2017 9:22 AM