Forum Discussion

parzival1220's avatar
parzival1220
Frequent Visitor
2 years ago

DATETIME Column

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

Need Help

3 Replies

  • HotChilli's avatar
    HotChilli
    Community Champion
    2 years ago

    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's avatar
      parzival1220
      Frequent Visitor
      2 years ago

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

       

      Thanks again.

  • v-zhangti's avatar
    v-zhangti
    Community Support
    2 years ago

    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.