Uploaded Excel File to SharePoint List Import | parse the date field if not in right format

(0) Share

Report

Posted on by Mini_R53

Hi,

Can anyone help on how to write the expression in powerautomate.

I have a date field in an excel file uploaded in SharePoint.

The supplier of the file sometimes sends the date data in numerical format.

For example 45368 is 17/03/2024 (Australian Format).

How do I write this to correct the value if not in the right format?

I have tried this and it didn't work.

if(equals(item()?['MyOwnDate'], string(parseDateTime(item()?['MyOwnDate'], 'en-AU', 'd/MM/yyyy'))),
formatDateTime(convertTimeZone(parseDateTime(item()?['MyOwnDate'], 'en-AU', 'd/MM/yyyy'), 'UTC', 'AUS Eastern Standard Time'), 'yyyy-MM-dd'),
formatDateTime(convertTimeZone(parseDateTime(item()?['MyOwnDate'], 'en-AU'), 'UTC', 'AUS Eastern Standard Time'), 'yyyy-MM-dd')
)

Thank you

Categories:

General topics

All responses (3)

Answers (0)

Sort by

Santhosh Balakrishnan 169 on at

Like
a
(0)

Report
Copy link

Link copied!

@sohc In list row present in a table action, change the Date Time Format option as a ISO 8601 to get a proper format on SharePoint list.

Let me know if this works for you.

If this solution proves helpful and resolves your issue, kindly consider marking it as accepted. Doing so will aid others who encounter similar challenges in the future.

Regards,
Santhosh Balakrishnan
Apps4.Pro - Trusted Office365 T2T Migration partner. Migrate Exchange, SharePoint, Microsoft Teams, Stream, Yammer, Power Platform between tenants.
Product Specialist - http://www.apps4.pro/
Mini_R53 23 on at

Like
a
(1)

Report
Copy link

Link copied!

Thanks Santhosh - I have found a solution using this command and I will share it for others who may need it.

formatDateTime(adddays('1899-12-30', int(item()?['SharePointFieldName'])), 'yyyy-MM-dd')
Santhosh Balakrishnan 169 on at

Like
a
(0)

Report
Copy link

Link copied!

If this solution proves helpful and resolves your issue, kindly consider marking it as accepted. Doing so will aid others who encounter similar challenges in the future.

Regards,
Santhosh Balakrishnan
Apps4.Pro - Trusted Office365 T2T Migration partner. Migrate Exchange, SharePoint, Microsoft Teams, Stream, Yammer, Power Platform between tenants.
Product Specialist - http://www.apps4.pro/