How to best Create List from Excel w/Blank Dates

[Phishdawg]

The blanks in 'OutTime' still have me struggling with creating a new SP List. It also seems like I'm doing conversions on both sides of this, uneccessarily.

There are times when the source Excel files has blanks (I've confirmed no hidden characters) in the 'OutTime' (D) column.

In Excel, I am using =IF(F2="","",TEXT(F2-D2,"h:mm:ss")) in 'WaitTime' to account for the blank dates and calculate the difference between 'OutTime' and 'InTime'.

'InTime' and 'OutTime' are formatted as Date, 'WaitTime' is formatted as Time.

In the Power Automate flow I'm using a 'Create item' action with the following, but I keep getting ISO 8601 error messages, or the items with no blanks will load to the list but the blanks don't.

'InTime' Expression = addMinutes(items('Apply_to_each')?['InTime'],420)

'OutTime Expression = If(equals(items('Apply_to_each')?['OutTime'],'InTime'),null,addMinutes(items('Apply_to_each')?['OutTime'],420))

'WaitTime' Expression = dateDifference('1899-12-30',items('Apply_to_each_2')?['WaitTime'])

[#2]

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.

[#3]

I'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

[#4]

This 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 🙂

[#5]

Do 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.