Time Duration to Date Conversion

[Anonymous]

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

Duration	Date
3w 1d 18h 36m	DD-MM-YYYY  HH:MM:00
6d 19h 59m	DD-MM-YYYY  HH:MM:00
2w 0d 21h 14m	DD-MM-YYYY  HH:MM:00
21h 30m	DD-MM-YYYY  HH:MM:00

[Anonymous]

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]

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.

[amitchandak]

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

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
Month	Week	Day	hours	minutes
1	2	5	18	54
	-	1	1	3
	-	-	2	20
	-	-	-	10

[Anonymous]

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]

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.

[VahidDM]

HI @Anonymous 

Can you please add some sample result under the Date column?

Appreciate your Kudos!! 

LinkedIn | Twitter | Blog | YouTube 

[Anonymous]

Hi Vahid,

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