DATETIME Column

[parzival1220]

Hi there,

I have a datetime field in the format "20/11/2023 20:33:45". I would like to create a new column that ages this datetime value against the current system datetime value (e.g. today's date and time). Ideally, this new column will return a number of days / hours / minutes between the two dates (e.g. 1 days, 3 hours, 2 mins and 5 seconds). When I look at the options in Power Query, there doesn't seem to be one to age the date and time together, only date or time etc. 

Please could I ask if there is another way to calculate the whole value rather than just date or time?

Many thanks. 

Jason

[v-zhangti]

Hi, @parzival1220 

You can try the following methods.

Column:

Now = NOW()

Second = DATEDIFF([Datetime],[Now],SECOND)

New Time = 
var vSeconds=[Second]
var vMinutes=int( vSeconds/60)
var vRemainingSeconds=MOD(vSeconds, 60)
var vHours=INT(vMinutes/60)
var vRemainingMinutes=MOD(vMinutes,60)
var vDays=INT(vHours/24)
var vRemainingHours=MOD(vHours,24)
return
  vDays&" Days & "&
  vRemainingHours&" Hours & "&
  vRemainingMinutes&" Minutes & "& 
  ROUND(vRemainingSeconds,0)& " Seconds"

Is this the result you expect? Please see the attached document.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[HotChilli]

On the 'Add Column' tab, If you select both columns, you can use the Time section (it will activate a Subtract menu item).  This will subtract one column from the other and give you a duration.  This is when the fun starts.

You can use duration in Power Query but DAX doesn't know how to deal with it in a way that you probably want.  There are lots of functions to give you minutes from a duration and (on the forum and the web) you will find ways to get that as a text field in days,hrs, minutes, seconds

[parzival1220]

Apologies for the late reply and thank you for the suggestions. I will have a play with Power Query and report back 😀.

Thanks again.