Excel Table to SharePoint List flow has a date issue

[fnanfne]

Hey Power People

I have an Excel table with a date column and a corresponding SharePoint list also with that same date column called Date. In Power Automate, I want to list the rows present in this table, then create an item in the SharePoint list for each row enumerated this way.

The problem is that Power Automate erroneously converts the date column into a text field with some 5-digit number, crikey, why?? Then SharePoint moans because its not the expected type, lol. I tried to replace the Date-type field with a TXT-type field on SharePoint and the flow now works but the date comes through as this arbitrary 5-digit number, like 47238. What's worse, I can't even explicitly tell Excel to observe this column as a TXT one, it automatically converts to a Date type whenever I enter something looking like "15/05/2017".

I found some code that converts this 5-digit number into the correct dd-MM-yyyy format, which now corrects the date to a format I want; 15/05/2017 but again, SharePoint moans that the type is not correct, oh woe is me.

I then thought of converting this TXT field into a Date, in the hope that SharePoint will accept it but alas, still stuck.

Is there a better way to address this issue? Perhaps by not even doing any conversion/composing to begin with? Do I need to nest another 'Apply To' in the flow somewhere?

Here is the code in my first compose called "Compose 2"

addDays('31-12-1899',int(item()['Date']),'dd-MM-yyyy')

Here is the code in my second compose called "Compose 3"

formatDateTime(item()['Compose_2'], 'dd-MM-yyyy')

Compose 3 throws an error...
"InvalidTemplate. Unable to process template language expressions in action 'Compose_3' inputs at line '0' and column '0': 'The template language function 'item' must not have any parameters.'."

When I change the expression to read items instead of item, the flow fails to save with the following error...

"Flow save failed with code 'InvalidTemplate' and message 'The template validation failed: 'The workflow action 'Compose_3' at line '1 and column '3080' references the action 'Compose_2' of type 'Compose': only the actions of type 'foreach' are allowed to be referenced by 'repeatItems' or 'items' functions.'.'."

Here is a pic of the flow..

Any help greatly appreciated

[ManishSolanki · Accepted solution]

Hi @fnanfne 

You don't need nested 'Apply to each FixingDate' action to format the date column. You can format the date time in single compose action using expression.

The below expression can be used in 'Compose 2' action is:

addDays('1899-12-31',int(item()?['Date']),'yyyy-MM-dd')

If you are just copying rows from excel to SharePoint list, the overall flow should look like:

If this helps, please remember to give a 👍 and accept my solution as it will help others in the future.

Thanks

[RobElliott · Accepted solution]

@fnanfne internally Excel stores dates as an integer not as what we would know as a date. These days you don't need to do the addDays expression suggested by @ManishSolanki (although that works very well); in your list rows present in a table action click on show advanced options and in the DateTime Format field select ISO 8601. That will convert the date to a normal date format which you can use later in your flow.

Rob
Los Gallardos
If I've answered your question or solved your problem, please mark this question as answered. This helps others who have the same question find a solution quickly via the forum search. If you liked my response, please consider giving it a thumbs up. Thanks.

[fnanfne]

Thank you @ManishSolanki and @RobElliott !

Both your approaches work great. I will choose the easier route proposed by RobElliot.

Thanks again guys, massively appreciated!