Support using 2 different dates to create the life time.

[FelippeAzevedo7]

I would like to ask for help because I have a report that I am migrating from excel to power bi.
In it I have 2 different dates, one for main and one for activation.
For main I use 2 columns with start and end date, but when it comes to activation I use two other start and end dates.
I tried to reproduce my excel formula and it didn't work very well.
Are there any good tips?

Formula I use in excel: =IF(T5="Activation",INT(Q5-P5)&" days"&TEXT(Q5-P5,"h"" hrs ""m"" mins """),INT(L5-K5)&" days"&TEXT(L5-K5,"h"" hrs ""m"" mins """))

[xifeng_L · Accepted solution]

Hi @FelippeAzevedo7 ,

Just change the TEXT in your formula to FORMAT, such as:

=IF(T5="Activation",INT(Q5-P5)&" days"&FORMAT(Q5-P5,"h"" hrs ""m"" mins """),INT(L5-K5)&" days"&FORMAT(L5-K5,"h"" hrs ""m"" mins """))

Note: Cell references such as T5 and Q5 need to be changed to the corresponding columns.

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

Thank you~

[FelippeAzevedo7]

Hello

Could you check if the formula is correct?

LifeTime = IF(Sheet1[Main or Activation]="Activation",INT(Sheet1[Activation End date].[Date]-Sheet1[Activation Start date].[Date])&" days "&FORMAT(Sheet1[Activation End date].[Date]-Sheet1[Activation Start date].[Date],"h"" hrs ""m"" mins """),INT(Sheet1[Closure Date (UTC)].[Date]-Sheet1[Start Date (UTC)].[Date])&" days "&FORMAT(Sheet1[Closure Date (UTC)].[Date]-Sheet1[Start Date (UTC)].[Date],"h"" hrs ""m"" mins """)) 

The formula is only showing the days and no hrs or mins.

Could you help me to solve it?

Tks

[xifeng_L]

If Table[Date] is a date containing a time, e.g. 2024-1-1 13:45, then Table[Date]. [Date] then always returns the date part, i.e. 2024-1-1, so that the hours and minutes in the result are 0.

I'm guessing it's due to this

[FelippeAzevedo7]

Hello again

Many tks for the information. It worked perfectly!