none
Get Total Hours and Minutes on Calculated Column

    Question

  • Hello everyone,

    This is my current scenario. I have 4 Date Time columns in a SharePoint list: Job Start,  Job Pause, Job Resume, and Job Stop. When the user updates the job status to either Started, Paused, Resumed, or Stopped, a workflow triggers and uses the current Modified value and sets it to the respective column.

    What I am trying to achieve is I need to calculate the total hours and minutes from when the job started until it stopped by calculating all 4 columns. The problem with this is that the Job Pause and Job Resume columns might be empty, and so the calculation will only be between the Job Start and Job Stop.

    If anybody knows the best approach for this and can point me in the right direction, I'd really appreciate it.

    Thanks in advanced.


    Fausto Capellan, Jr - SharePoint Admin

    Monday, April 11, 2016 5:00 PM

Answers

  • Hi

    i recommend you a calculated column which will do the job

    The formula it's this one

    =IF(OR(YEAR(JobPause)=1899;YEAR(JobResume)=1899);TEXT(JobStop-JobSTart;"h:mm");CONCATENATE(INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60);":";IF(INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60)>9;INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60);CONCATENATE("0";INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60)))))


    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.

    Monday, April 11, 2016 8:33 PM

All replies

  • You confused me a bit in your 2nd paragraph where you say you need to calculate using all four columns, but then say it will actually only be between the two Start and Stop columns. So, do you need to simply calculate Stop Date/Time minus Start Date/Time and get the entire duration? OR do you need to account for the subtraction of pauses, giving more of an "active runtime" rather than full duration?

    Monday, April 11, 2016 5:41 PM
  • Hi John,

    Thanks for your reply and sorry about the confusion. To answer your questions above, I need to get the full duration from when the job was started, paused, resumed, and stopped, taking into consideration the fact that both Paused and Resumed might be empty at times.


    Fausto Capellan, Jr - SharePoint Admin

    Monday, April 11, 2016 8:26 PM
  • Hi

    i recommend you a calculated column which will do the job

    The formula it's this one

    =IF(OR(YEAR(JobPause)=1899;YEAR(JobResume)=1899);TEXT(JobStop-JobSTart;"h:mm");CONCATENATE(INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60);":";IF(INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60)>9;INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60);CONCATENATE("0";INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440)-60*INT((INT((JobStop-JobSTart)*1440)-INT((JobResume-JobPause)*1440))/60)))))


    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.

    Monday, April 11, 2016 8:33 PM
  • Hi Romeo,

    Thank you for the formula. I will add it to the calculated column and will post back the results.


    Fausto Capellan, Jr - SharePoint Admin

    Wednesday, April 13, 2016 8:46 PM
  • Hi Romeo,

    That formula worked perfectly.

    Thank you very much.


    Fausto Capellan, Jr - SharePoint Admin

    Thursday, April 14, 2016 4:27 PM
  • Welcome

    More details in this wiki technet or on my blog site


    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, April 15, 2016 6:15 AM