Current Date (without time ) to Excel as a Date format merrygoround...

(0) Share

Report

Posted on by UK_Mike

Hi All,

As per subject, current date (without time) formatted as a date so i can write it to Excel.

What bloody annoys me is the option is there "current date only " or "current date and time" 🙄

Thanks, Mike.

Categories:

Power Automate Desktop

I have the same question (0)

All responses (10)

Answers (0)

MichaelAnnis 5,727 Moderator on at

Like (0)

Report

Everything you need to know on Dates:
https://www.linkedin.com/pulse/power-automate-desktop-pad-best-practices-part-1-date-michael-annis/

Here's a quick example:

Let me know if you need help, but the article has everything spelled out.

Good luck!

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report

Hi Mike,

Ive decided to make a very large pot of coffee...
Jeez...
Erm... I will come back to ya.

Many thanks as always.

I hereby accuse Microsoft of employing at least 1 sadist...

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report
Yeah, this one baffles me why conversion isn't included, but we can get there. I have a "Get Dates" subflow that I built once and copy flow to flow. So it gets:
Today
Yesterday
Beginning Day This Month
Ending Day Last Month
Beginning Day Last Month
Ending Day Previous Month
All of those in both Date and Text formats, and then I just make changes as I need, because sometimes I need MMddyyyy, but sometimes MM.dd.yy, so the text format changes constantly.

You shouldn't have an issue.

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report

Hi Mike,

Tried but failed miserably....... in the end my frustrations got the better of me.

I tried copying your code into a blank flow but there was a lot of errors, am I supposed to have something preceding your code ? I will definitely keep that one as a separate flow to call upon.

It would help me greatly if you could provide me with current date in Euro format, 27/1/22, formatted as a date for writing to Excel.

You name it, I tried it, splitting text etc.

Thanks, Mike.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

Apologies...my code may be outdated...I am on 2.8. Lame, I know, but too many people complain about upgrading killing their bots and I don't have the time to upgrade safely.

Get Current Date and Time
Format %CurrentDateTime% to Text Custom format d/M/yy to %TODA_dmyy_Slash%

Make sure the M is capital. Lowercase m stands for minutes, not month.

Then you should be able to write %TODA_dmyy_Slash% to Excel.

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report

Then you should be able to write %TODA_dmyy_Slash% to Excel.
Isnt that just a text value ?

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

Right, but if I paste a text value in a date format into Excel, doesn't Excel typically recognize that it's a date and format appropriately? There may be slight differences because of the UK US formatting, but try the "ShortDate" method, because I think your regional settings should put it to the proper short date format.

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report

I can write the date I require or any other date etc no problem to Excel, its just that Excel recognises it as text, not a date format.
In the meantime ive been hardcoding =TODAY() , its getting me by but not ideal lol.

Column is already formatted as date but Excel still converts it to text.

Was this reply helpful? Yes No
MichaelAnnis 5,727 Moderator on at

Like (0)

Report

Mike, I would get with @Anonymous and see if he knows how it rectified itself:

https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Date-Format-not-exporting-correctly-from-PAD-to-Excel/m-p/1255552

I have another suggestion in that post for pasting the US date and then using the UI elements to convert the column to a different date format.

Was this reply helpful? Yes No
UK_Mike on at

Like (0)

Report

Hey Mike,

Im giving up at this stage lol, messing about with Excel, attached a pic.
If its not self explanatory, a simple text file was read, contents were 27/1/22
2 variables were read plus manually inputted 27/1/22
Columns were formatted as per headers...
Even the CurrentDateTime var which is a date variable aligns left ?

Was this reply helpful? Yes No