Excel Forum • My Online Training Hub

Office Script - Power Automate / Data Type Issues with setNumberFormat

Peter Fresh

Member

Members

Forum Posts: 13

Member Since:
August 6, 2020

Offline

December 22, 2022 - 8:17 pm

Hi @all,

I am currently working on a project where I need to run an office script through Power Automate.

In the associated excel file, I have a column with a date formatted as a string.

Using an Excel formula, I want to change the format to a date value.

For this I use the following code:

// getting the date string and pull out year and month information for further usage

let date_string = selectedSheet.getRange("A2").getValue() as string;

let jahr_str = date_string.substring(6, 10);

let monat_str = date_string.substring(3, 4);

let jahr_int = parseInt(jahr_str);

let monat_int = parseInt(monat_str);

let jahr_monat = jahr_str + "_" + monat_str;

// converting the date and employee id string values into date value and number value via excel formula

selectedSheet

.getRange("AR2:AS2")

.setFormulas([

[

'=IF(tblschuiexport[Datum]<>"",DATEVALUE(tblschuiexport[Datum]),"")',

'=IF(tblschuiexport[Personalnummer]<>"",NUMBERVALUE(tblschuiexport[Personalnummer]),"")',

]);

// Paste result of conversion into the table

tblschuiexport

.getColumn("Datum")

.getRangeBetweenHeaderAndTotal()

.getRow(0)

.copyFrom(

selectedSheet

.getRange("AR2:AS2")

.getExtendedRange(ExcelScript.KeyboardDirection.down),

ExcelScript.RangeCopyType.values,

false,

false

);

// Set number format for date column

tblschuiexport

.getColumn("Datum")

.getRangeBetweenHeaderAndTotal()

.getRow(0)

.getResizedRange(parseInt(last_row)-1, 0)

.setNumberFormat("dd.mm.yyyy");

Problem: When I run this script in Excel, everything is fine. However, when I run the script with Power Automate, I get a lot of #VALUE errors in the date column.

I assume this is due to different language settings?! But how can I fix this - does anyone have any ideas?

For further understanding I attach two screenshots and an anonymized sample file as well as the office script file.

Thanks in advance!!

Best Regards,

Peter

Attachments

Screenshot_after_execution_from_pa.png (198 KB)
Screenshot_before_execution_from_pa.png (183 KB)

See Answer

Velouria

London or thereabouts

Member

Members

Trusted Members

Forum Posts: 574

Member Since:
November 1, 2018

Offline

December 22, 2022 - 8:51 pm

Instead of using DATEVALUE, I'd suggest using DATE(RIGHT(tblschuiexport[Datum],4),MID(tblschuiexport[Datum],4,2),LEFT(tblschuiexport[Datum],2))

Answers Post

Peter Fresh

Member

Members

Forum Posts: 13

Member Since:
August 6, 2020

Offline

December 22, 2022 - 9:22 pm

Thanks for the idea to change the way to get the date value.
It just fixed the problem!!!

Velouria

London or thereabouts

Member

Members

Trusted Members

Forum Posts: 574

Member Since:
November 1, 2018

Offline

December 27, 2022 - 9:55 am

You're welcome, and merry Christmas 🙂

Forum Timezone: Australia/Brisbane

Most Users Ever Online: 170

Currently Online: Norilina Harvel, Jose Molina, Brenda Krol

Guest(s) 56

Currently Browsing this Page:
1 Guest(s)