Importing dates from Excel into Sharepoint list

[TSimsD]

Hi,

I've tried various forums for answers to this issue but none have succeeded. I have an extensive spreadsheet that I want to import a Sharepoint list. The issue is the date column. irrespective of how I format the date in Excel it does not conform to the date/time format in Sharepoint.

The error message (note the error varies depending on how I format in excel)

I am sure there should be a simple function fix but I suspect I don't have sufficient knowledge to understand potential solutions offered on other forums.

any help/advice gratefully received.

[#2]

@TSimsD 

Note the example shown is a test file I'm using as the working sheet is too large to easily manipulate

[#3]

@TSimsD Excel stores dates as an integer of the number of days since 30 December 1899. So you need to convert that integer to a date before your flow can use the output in SharePoint.

So you will need to add Compose control.

The table in my spreadsheet has a column called Time:

In your flow you'll need to convert that column using an expression in a Compose control:

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Time']),-2)), 'yyyy-MM-dd')

(the way I do it is to use 1900-01-01 for ease of remembering rather than 1899-12-30, then take off 2 days from the result)

You then use the output of that Compose in the relevant field in your SharePoint Create item action.

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
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.

[#4]

Hello @RobElliott 

I have the same scenario as your solution and have tried your expression in my workflow. However, I am getting an error: 

 "Unable to process template language expressions in action 'ComposeFormatExcelDate' inputs at line '1' and column '9734': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'."

Can you assist?

[#5]

@sumitarjandas I am getting the same error and really need some help sorting it out. I’ve tried it all!

[#6]

Hello All,

I agree with the approach that @RobElliott provided.

I've done this before but using a variable (vs compose) to convert and store the Excel date. More details here: Import Excel data into an existing SharePoint List using Power Automate – Norm Young

I hope this helps.

Norm

[#7]

@stormin_30 

Thanks Norm, I will digest and try

Cheers

Tony

[#8]

@sumitarjandas - Did you get a resolution on the issue?

"Unable to process template language expressions in action 'ComposeFormatExcelDate' inputs at line '1' and column '9734': 'The template language function 'int' was invoked with a parameter that is not valid. The value cannot be converted to the target type.'."

[#9]

Hi, I've imported lots of Excel files with dates in it.
The expression I use succesfully is:
addDays('1899-12-30',int(items('Apply_to_each')?['Date']),'yyyy-MM-dd')

[#10]

thanks for the response and yeah that works but its when the date field is blank is what the issue is for me. tried numerous if equal/empty etc, cannot get to work so far

[#11]

Hi, if it is okay to have an empty value in the SP list you could try:
if(empty(items('Apply_to_each')?['Date']), null, addDays('1899-12-30',int(items('Apply_to_each')?['Date']),'yyyy-MM-dd'))

[#12]

I have had a similar issue with dates in all formats in Excel formatting to numbers in SharePoint lists. I found a simple solution. 

Did you format yours individual fields after importing but before creating the list. At this stage, SharePoint displays your fields and you have the option to change each field before creating your list. When I clicked on the relevant field it gave the option to change to date and time field. Then, after creating the list, I could remove the time option in the field drop down column settings -edit - remove time' option. However, it only displays the date in US format.  @TSimsD 

[#13]

@TSimsDFor other who are looking for an answer, you can use the following tip : in the "List rows prensent in a table" card, expand the advanced options and select "ISO 8601" in the date / format option.