Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply

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 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 """))

1 ACCEPTED SOLUTION
xifeng_L
Super User
Super User

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~

 

View solution in original post

4 REPLIES 4
xifeng_L
Super User
Super User

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~

 

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

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

Hello again

 

Many tks for the information. It worked perfectly!

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.