Forum Discussion

Helper I

2 years ago

Solved

Support using 2 different dates to create the life time.

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 s...

xifeng_L
2 years ago
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

Helper I

2 years ago

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

Super User

2 years ago

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