Changing excel number formatting to be accepted in Power Automate

[MMDDW]

Hello,

i have the following issue.

There is an excel script, which creates a table. inside this table i have column (T in my case) which holds the following type of values:

what i want to do is take those values and process them inside power automate. The issue power automate does not read this as a date and when i change the numbering to something else (for example "mm-yyyy") its not reflected in the column. 

When i go inside the cell itself and then press enter it changes to the required format and  i can process this data inside my flow:

But i cannot do this cell by cell when the input is in the thousands.

Maybe this is something simple, but my excel skills are not great, so my question is -

a) is there a way to auto-refresh all the cells in the specified column so the new formatting is applied

or maybe

b) a solution to my problem inside power automate

I've read this article which describes my predicament and possible solutions perfectly - https://www.exceldemy.com/excel-formatting-not-working-unless-double-click-cell/?fbclid=IwAR1YtX_udc...

But unfortunately the Record script action in excel is not able to replicate this action and cant write the syntax necessary on my own.

thanks in advance!

M

[trice602]

Hi @MMDDW ,

You can do this with a quick Office Script and Power Automate flow.  I will give you an example here and the TypeScript to copy and paste in.

The first thing you want to do is open up your Excel workbook in a browser because that is the only place you and add an Office Script (as of August 2023).

Click on the Automate tab and then New Script.

Here's your TypeScript to cope/paste.

function main(workbook: ExcelScript.Workbook) {
  let selectedSheet = workbook.getActiveWorksheet();
  selectedSheet.getRange("T:T").setNumberFormat("MM/dd/yyyy");
}

Replace the default code with yours above.

Save, Close.

Back to Power Automate.

Here you have a simple trigger button and one action.

Save and Run.  Results are below.

This is a great option especially if you have thousands of rows and need a quick solution.

[MMDDW]

Hi Tom,

thanks for the reply. 

This is something I've tried already but it did not work - 

I trigger the flow, run the script, the table is created but the format has not changed:

this is reflected in power automate as well:

its still no accepted as a date format and fails at a later stage:

so i am not sure why excel is not reflecting the changes to the numbering immediately but its whats causing me the issues. 

[trice602]

Hi @MMDDW ,

No worries, you are very close.  When I look at your Office Scripts, you have some extra steps in there that may be the root cause.  In my example, I am just referring to column T.  Please check my code vs. yours, paste in mine, and resubmit please.

[trice602]

Hi @MMDDW ,

One more thing:  In my test, with Excel open, I formatted the entire column as a Date MMMM-yyyy (custom value) just like in your original example.  it is possible that these values are strings in the current state.

[SamLed]

@trice602 

Hi Tom,

I did some test, you're last guess looks to be the right one. I've forced data format as text and wasn't able to format it back to date.

Sam

[trice602]

Thanks @SamLed .  Yeah I was trying to remember the steps I did in testing and when I tried to make my initial column look like @MMDDW , I formatted first.  I think this will work with the extra, manual setup step.

[SamLed]

Succeed by using the DATEVALUE formula and giving it the desired format directly from Excel.

[MMDDW]

@trice602 @trice602 

indeed the extra step was the key and what @SamLed suggested using DATEVALUE beforehand worked like a charm! 

Thank you both!