Forum Discussion
Apr 04, 2023
If you are trying to create a SharePoint list from an Excel file that has blank dates in the 'OutTime' column, you may encounter some errors related to the ISO 8601 format. This format is a standard way of representing date and time values that is compatible with Power Automate and other services. So if you want to work with creating a list from Excel with blank dates using Power Automate, you can follow this:
The first step is to make sure that your Excel file has the correct format for the 'InTime', 'OutTime' and 'WaitTime' columns. The 'InTime' and 'OutTime' columns should be formatted as Date, and the 'WaitTime' column should be formatted as Time. You can use the formula =IF(F2="","",TEXT(F2-D2,"h:mm:ss")) in 'WaitTime' to account for the blank dates and calculate the difference between 'OutTime' and 'InTime'.
The next step is to create a flow that will read the Excel file and create a SharePoint list item for each row. You can use the 'List rows present in a table' action to get the data from Excel, and then use an 'Apply to each' loop to iterate over each row. Inside the loop, you can use the 'Create item' action to create a list item with the following expressions:
- 'InTime' Expression = addMinutes(items('Apply_to_each')?['InTime'],420)
- 'OutTime Expression = If(equals(items('Apply_to_each')?['OutTime'],''),null,addMinutes(items('Apply_to_each')?['OutTime'],420))
- 'WaitTime' Expression = dateDifference('1899-12-30',items('Apply_to_each_2')?['WaitTime'])
The addMinutes function is used to adjust the time zone difference between Excel and SharePoint, which is 420 minutes in my case. You can change this value according to your time zone.
The If function is used to check if the 'OutTime' column is blank, and if so, set it to null. Otherwise, it adds the time zone difference as well.
The dateDifference function is used to convert the 'WaitTime' value from a time format to a duration format that SharePoint can understand.
By using these expressions, you should be able to create a SharePoint list from an Excel file with blank dates without getting any ISO 8601 errors.
The first step is to make sure that your Excel file has the correct format for the 'InTime', 'OutTime' and 'WaitTime' columns. The 'InTime' and 'OutTime' columns should be formatted as Date, and the 'WaitTime' column should be formatted as Time. You can use the formula =IF(F2="","",TEXT(F2-D2,"h:mm:ss")) in 'WaitTime' to account for the blank dates and calculate the difference between 'OutTime' and 'InTime'.
The next step is to create a flow that will read the Excel file and create a SharePoint list item for each row. You can use the 'List rows present in a table' action to get the data from Excel, and then use an 'Apply to each' loop to iterate over each row. Inside the loop, you can use the 'Create item' action to create a list item with the following expressions:
- 'InTime' Expression = addMinutes(items('Apply_to_each')?['InTime'],420)
- 'OutTime Expression = If(equals(items('Apply_to_each')?['OutTime'],''),null,addMinutes(items('Apply_to_each')?['OutTime'],420))
- 'WaitTime' Expression = dateDifference('1899-12-30',items('Apply_to_each_2')?['WaitTime'])
The addMinutes function is used to adjust the time zone difference between Excel and SharePoint, which is 420 minutes in my case. You can change this value according to your time zone.
The If function is used to check if the 'OutTime' column is blank, and if so, set it to null. Otherwise, it adds the time zone difference as well.
The dateDifference function is used to convert the 'WaitTime' value from a time format to a duration format that SharePoint can understand.
By using these expressions, you should be able to create a SharePoint list from an Excel file with blank dates without getting any ISO 8601 errors.
- PhishdawgApr 04, 2023Brass ContributorI've managed to get Excel to place the 'InTime' in the 'OutTime' when 'OutTime' is blank; This provides a 'WaitTime' of 00:00:00, which is acceptable.
I've used the datediff you recommend before.
It returns a value that looks like the following, where the time is correct, but the date (unwanted) - in decimal, is present.
When 'OutTime' is 'InTime' - 45020.00:00:00
When 'OutTime' has a date - 45020.00:014:23- Apr 04, 2023This is because the DATEDIFF function does not return a date or time value, but an integer value. To convert this integer value to a time value, you need to use another function such as DATEADD or CAST . For example, you can use the following formula to get the difference between two dates in hours as a time value:
=DATEADD(hour,DATEDIFF(hour,'OutTime','InTime'),0)
or
=CAST(DATEDIFF(hour,'OutTime','InTime') AS TIME)
These formulas will return a value that looks like 00:00:00 when 'OutTime' is 'InTime', and 14:23:00 when 'OutTime' has a date.
Hope this helps 🙂- PhishdawgApr 04, 2023Brass ContributorDo you mean I should use one of the formulas in the Excel 'WaitTime' column?
DATEADD(hour,DATEDIFF(hour,'OutTime','InTime'),0) did not work as an expression in Power Automate.