Do you need to convert an Excel date to a SharePoint list date in your flow?
Many Power Automate users struggle to import an Excel file into a SharePoint list using Power Automate. An issue arises when it comes to the date format and serial numbers used in Excel to represent dates. This is different in Excel than what SharePoint expects.
Fortunately, there are a few solutions that can handle this problem. The first method is to format the date column in Excel as a string rather than as a date/time value. This should prevent any inconsistencies between the two applications.
The second suggestion is to use the DateTime Format options in the List rows present in a table action in Power Automate to make sure that the date format is correctly understood by SharePoint.
This post is going to show you how to implement these solutions so your Excel dates can be entered in your SharePoint lists correctly.
Convert Excel Date to SharePoint with the TEXT Function
Excel dates are actually serial numbers starting at 1 for the date 1900-01-01 and increasing by 1 for each day.
When viewed in Excel they have a date format applied that makes them look like a date instead of the underlying serial number.
Unfortunately, when this data is imported in Power Automate, it’s the underlying serial number that is returned. This means when you try and add the date into SharePoint it is going to try and enter a number in a date column.
This will result in an error in your flow.
The first method will convert this date serial number to a text value of the date with the help of the TEXT function in Excel.
This function allows you to convert any number to text with your required format. This way the value that can be returned in Power Automate is a text string of your date in the format yyyy-mm-dd. This is what the SharePoint date column is expecting.
You will need to create a new calculated column in your table that is based on the date column.
= TEXT ( [@Date], "yyyy-mm-dd" )
The above formula can be added to your table. This will convert the date in the Date column to a text value with the format yyyy-mm-dd
.
This column will You can then use this new column to add or update your SharePoint date columns.
Convert Excel Date to SharePoint with the DateTime Format Options
The TEXT function is an easy method to implement, but it will add clutter to your data table in Excel as it requires adding a new column to your table.
You can avoid this by using a Power Automate option in the List rows present in a table action from the Excel connector in Power Automate.
This will force Power Automate to return the date as a text value instead of the serial number and you can avoid the extra calculated column from the first method.
Add the List rows present in a table action from the Excel connector into your flow and fill in the required Location, Document Library, File, and Table inputs.
Then click on the Show advanced options link at the bottom of the action.
This will reveal more option including the DateTime Format. Choose the ISO 8601 option from the dropdown menu.
Now the list rows action will return the serial number as a date in the format yyyy-MM-ddTHH:mm:ss.fffZ
.
yyyy
is the year.MM
is the month.dd
is the day.HH
is the hour in 24 hour format.mm
is the minutes.ss
is the seconds.fff
is the decimal representing the fraction of seconds.
When you only have the date portion of the date and time in Excel, the HH
, mm
, ss
, and fff
values should be zero.
This is the required format for adding a date to a date column in SharePoint and the flow should run without error.
Conclusions
Power Automate will show Excel dates as the underlying serial number by default. This will cause problems when trying to add date values from Excel to a SharePoint list as the flow will fail with an error.
You can use the TEXT function in Excel to force the dates to the text values that SharePoint is expecting.
Another option is to set the DateTime Format option within the Excel list rows action to return the date as a text value instead of serial number.
Did you come across this problem with adding Excel dates to SharePoint via Power Automate? How did you solve the problem? Let me know in the comments!
0 Comments