Forum Discussion
Solved
Convert Excel DateTime for Create item SP List Creation
I have issue creating a new SP List from Excel with Power Automate.
I can get all of the column to come over except the DateTime formatted columns, shown below.
Excel Sheet Screenshot - If you click in the D or F the content actually shows as '01/22/2022 7:16:54 AM' (or whatever the date happened to be at the time the entry was made.
I found a forum post that suggests using variables for the three columns. However, the 'int' of the formula keeps kicking out an error.
1. Initialize variable as a string
2. Set each variable with the column corresponding DateTime field from Excel
3. Use the following expression in a condition to convert the data for 'Create items' action for the
SP List data
For the Date (which currently have the date and the time) -
addDays(‘1899-12-30’,int(variables(‘varDate1′)),’yyyy-MM-dd’)
For the 'WaitTime' (derived by subtracting 'InTime' from 'OutTime' in Excel), should be displayed
in minutes and seconds. The recommendation was to use -
addDays(‘1899-12-30’,int(first(split(variables(‘varDate1’), ‘.’))),’yyyy-MM-dd’)
Phishdawg in your Excel list rows present in a table action you need to open the advanced options and in the dateTime Format field select ISO 8601. It will then bring the dates over in the correct format that you can use in your create item action.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
2 Replies
Phishdawg Check if using formatDateTime function helps as given here: Power automate, copying date column from online excel to SharePoint list
Example:
formatDateTime('10/30/2023')Please click Mark as Best Response & Like if my post helped you to solve your issue. This will help others to find the correct solution easily. It also closes the item. If the post was useful in other ways, please consider giving it Like.
For SharePoint/Power Platform blogs, visit: Ganesh Sanap Blogs
Phishdawg in your Excel list rows present in a table action you need to open the advanced options and in the dateTime Format field select ISO 8601. It will then bring the dates over in the correct format that you can use in your create item action.
Rob
Los Gallardos
Microsoft Power Automate Community Super User
