Forum Discussion

bikgx's avatar
bikgx
Frequent Visitor
3 years ago
Solved

get MIN, MAX DATE then count duration in hour

hi all;

 

i have data set, https://docs.google.com/spreadsheets/d/159xOcmrKj95YXYOayHmGi9A_K_8ZHIMq/edit?usp=sharing&ouid=100079262241875009342&rtpof=true&sd=true 

 

 

 

the point is; i already get MIN DATE, and MAX DATE ( with Measurement); but then how to get duration(hour) from that ? i tried using DATEDIFF formula but the result is wrong .

First Started =

CALCULATE (
MIN ( 'Data'[DateStarted] ),
ALLEXCEPT ( 'Data', Data[DateCreate], Data[worker] )
)

Last Finished =

CALCULATE (
MAX ( 'Data'[DateFinished ),
ALLEXCEPT ( 'Data', Data[DateCreate], Data[worker] )
)

duration(hour)= datediff([Last Finished] , [First Started],HOUR)

 

thank you for your help;

 

Azura

Maxdate
min
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi bikgx ,

    I have added some workers in the data to cross-check.

     

    1. Create new table as

    Table = SUMMARIZE(Sheet1,Sheet1[worker],"firstdate",MIN(Sheet1[DateStarted]),"lastdate",MAX(Sheet1[DateFinished]))
     
    2. Create new column as 
    hours = DATEDIFF('Table'[firstdate],'Table'[lastdate],HOUR)
     
    Output :

     

    Hope I have answered your question.

     

    Best Regards,
    Shreya Mukkawar

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi bikgx ,

     

    1.

    First Started = MIN(Sheet1[DateStarted])
    2. 
    Last Finished = MAX(Sheet1[DateFinished])
    3.
    Duration(Hours) = DATEDIFF(Sheet1[First Started],Sheet1[Last Finished],HOUR)
     

     

    Best Regards,
    Shreya Mukkawar

    Appreciate with a Kudos!! (Click the Thumbs Up Button)
    Did I answer your question? Mark my post as a solution!

     

    • bikgx's avatar
      bikgx
      Frequent Visitor
      3 years ago

      hi @Shhreya;

       

      are you using new measurement or new column ?

      i tried to create new column but the result is not what i expected; ineed all column i expected not just firt,last and duration and i have hundred rows raw data;

       

      best regards

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi bikgx ,

        I have added some workers in the data to cross-check.

         

        1. Create new table as

        Table = SUMMARIZE(Sheet1,Sheet1[worker],"firstdate",MIN(Sheet1[DateStarted]),"lastdate",MAX(Sheet1[DateFinished]))
         
        2. Create new column as 
        hours = DATEDIFF('Table'[firstdate],'Table'[lastdate],HOUR)
         
        Output :

         

        Hope I have answered your question.

         

        Best Regards,
        Shreya Mukkawar

        Appreciate with a Kudos!! (Click the Thumbs Up Button)
        Did I answer your question? Mark my post as a solution!