Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

Time Duration to Date Conversion

Hi ALL,

New Bee here.

I have a client request today to convert a Time Duration column to Date (As shown in the table). Can you experts please help me to achieve the exact date format with the code. Thanks in advance

 

DurationDate
3w 1d 18h 36mDD-MM-YYYY  HH:MM:00
6d 19h 59mDD-MM-YYYY  HH:MM:00
2w 0d 21h 14mDD-MM-YYYY  HH:MM:00
21h 30mDD-MM-YYYY  HH:MM:00
Need Help
Tips and Tricks

8 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    4 years ago

    Anonymous , You can split it on power Query using split by delimiters. Then you can remove the text.

     

    Then you add it date

    date(2022,01,01) + [week]+7 + [Day] + Time([Hour], [Minute], 0)

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Amit,

       

      Thanks for the reply. I did get your suggestion. 

      As my input is in Duration , how can we convert the same into a date column. Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak ,

       

      Can you please advice with another solution, As I need to use this DATE column in a DATEDIFF (Example : Candidate dropout since = DATEDIFF( createdate,date)

      where as date is the column which we i am trying to create now from the duration (shown in the above table).

       

      Please help,

      Thanks,Naveen

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi amitchandak :

      As I am new to Power BI, I got struck here.

      As per your suggestions, I did Split the delimiters and was able to achieve this . Can you please give me little more detailed steps on your second step (date(2022,01,01) + [week]+7 + [Day] + Time([Hour], [Minute], 0) ) where I was not able to frame a logic as per your suggestion.

       

      Also, recently month  is also added to the data with (Month Week Day Hour Minute)

       

      Please help to crack this logic.

       

       

      input:

      input
      Derived Date
      1M 2w 5d 18h 54m
      1d 1h 3m
      2h 20m
      10m
      output    
      MonthWeekDayhoursminutes
      1251854
       -113
       --220
       ---10

       

       

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Vahid,

       

      Thanks for the quick reply. Please find the sample data below: Thanks

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Anonymous ,

    Do you want to convert the field Duration to a date? But How did you get the duration field? Is the duration the duration from the creation time to a special day or some other date? For example, the last duration (21h 30m) is the duration from the creation date to today (28-06-2022 8:00:00) 21h 30m, so the last date value you want to get is 27-06-2022 11:30:00? Could you please give the duration, date field and your expected result with more explanation and screenshot? Thank you.

    Best Regards

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Hi Anonymous ,

      Thanks for responding,

      I got this duration field from my JIRA admin(as we are working with JIRA data in power BI) . 

      Yes, my request is to convert the duration column to date column( DD-MM-YYYY  HH:MM:00) so that I can refer this duration column in a DATEDIFF funtion where I have a created date minus with this Duration column.

       

      Actual formulae is-  DATEDIFF ([created], [Candidate Offboarded]),

      As candidate Offboarded is a duration column and created is a date column, I need to convert the Candidate Offboarded also into a date field so that my datediff will give me the  the duration again as needed.

      Sample Data: 

      Created Date

      25-05-2022 13:26:00

       Derived Date

      3w 0d 22h 44m

       

      And yes as per my understanding what you referred is correct- the last duration (21h 30m) is the duration from the creation date to today (28-06-2022 8:00:00) 21h 30m, so the last date value you want to get is 27-06-2022 11:30:00? 

      Thanks, Please do let me know if any inputs required from myside as i am a new bee and might miss few things.