Update the time part of a datetime value

[Walt1010]

I'd like to update the date part of a datetime value (eg

from 

08/07/2024 09:00:00)

to midnight 

08/07/2024 24:00:00

but I'm not sure if its just easier to extract the date and create a new datetime value, or to update the time in an existing value, and whether I have to do various conversions. Any ideas?

[Kedar_Pande]

You can write the below DAX:

NewDateTime = DATE(YEAR([DateTime]), MONTH([DateTime]), DAY([DateTime])) + TIME(0, 0, 0)

 This expression extracts the date portion from your existing DateTime field and resets the time to midnight. You can replace [DateTime] with the actual column name in your dataset.

If this post helps, please consider Accepting it as the solution to help the other members find it more quickly. Your Kudos/Likes are much appreciated!

Regards,

Kedar Pande

www.linkedin.com/in/kedar-pande

[ryan_mayu]

@Walt1010 

you can to change all the time to midnight? or only 9 am?

what's more, we don't have 24 for hour, should that be 00:00:00?

[Walt1010]

I want to change the time to midnight in all cases. Sorry I didn't realise it should be 00:00:00. I actually need to show midnight datetime as the next day's date plus 00:00:00.

[ryan_mayu · Accepted solution]

you can try this in PQ

=DateTime.From( Date.From([column]))

or 

=DateTime.From(Date.AddDays( Date.From([column]),1))

if you don't want the original column , you can remove it in the end

[Walt1010]

Thats great. Many thanks!

[ryan_mayu]

you are welcome