Power Query (Excel) convert date to mm/dd/yyyy The month and day has to be two characters.

[gheyman]

I have an excel Power query where I added a field where I wanted todays date. It works and produces todays date (8/18/2021). But where I am using the data it needs to be in a format where the month and day are both two characters (08/18/2021).

I am using :
Custom column formula
=DateTime.LocalNow()

how do I change this so I get the needed format? I am OK even if I need to create a second new field that converts this if needed. I just cant figure out any way to do this. Any help is very much appreciated.

= Table.AddColumn(#"Added Custom Resource", "Created", each DateTime.LocalNow())

Thanks

 

[GraH]

I get this below, which makes me wonder if the formula returns the format defined by the operating system in the regional settings.

Why do you need it converted to that format mm/dd/yyyy inside PQ? This can be done when the data is loaded in Excel.

EDIT:

Date.ToText(date, "mm/dd/yyyy")

allows to force the formats, but the date is then text. Not sure you want that.

 

[gheyman]

Thanks.

The data/table that is created is moved to a Template that is used to upload information into another software program. Thats the program that needs the date in a specific format. I tried to change the format on the excel tab / table but that didn't work when I tried to push the data to the other Software.

just for clarity, where I created the new column I should change the formula from: = =DateTime.LocalNow() to: Date.ToText(date, "mm/dd/yyyy")

Sorry, relatively new to Excel Power Query.

I really appreciate your help

 

[GraH]

I see, so the actual value matters, not simply the format. Seems then it must be text.
I meant like this

=Date.ToText(Date.From(DateTime.LocalNow()), "mm/dd/yyyy")

 

[gheyman]

= Table.AddColumn(#"Added Custom Resource", "Created", each Date.ToText(Date.From(DateTime.LocalNow()), "mm/dd/yyyy"))

In the applied steps (adding a new column under the formula, I added this: =Date.ToText(Date.From(DateTime.LocalNow()), "mm/dd/yyyy")

But this formula is resulting in a value of 00/18/2021 it should be 08/18/2021

Not sure what I did wrong.

Thanks

 

[gheyman]

OK

I changed it to:

= Table.AddColumn(#"Added Custom Resource", "Created", each Date.ToText(Date.From(DateTime.LocalNow()), "MM/dd/yyyy"))

capitalizing the MM made a difference

THANK YOU FOR YOUR HELP

 

[GraH]

Most welcome. Thanks for the feedback. Never thought "MM" makes such difference. Learned from this too. ?

 

[severynm]

The reason why: Date.From() and Date.FromText() both convert strings with the ISO 8601 date format, meaning capital m M is month and lower case m m refers to the minute.

Note that this is different in other places: DAX and Power BI both use a more intuitive system for date formats removing the case sensitivity: FORMAT function (DAX) - DAX

 

[GraH]

Thanks for the Intel, that's good to know. Strangely enough, it seemed to work on my end with just mm not MM.
Need to look into it some more.