Answered

DateTime Format Error

(0) Share

Report

Posted on by Phineas

5,331 Moderator

I ran away from this issue recently; Solved my problem another way.

Fate can be cruel, as I am back here again.

I have an Excel workbook with datetime columns.

I am trying to move the dataset from Excel to an SPList. Everything in the flow is working except the datetime data.

Field Names in both: HireDate, TempStartDate
Flow has a Compose for each with just has each field in it, no additional coding or expression
Excel column- ShortDate Format
SharePoint column - DateTime (Date Only)

The 'TempStartDate' column in Excel (source data) has blanks, which I think is causing the problem.

I am trying something like the below as an expression in the date fields of the 'Create item'; A piece of formula I came across.
I don't really need to add any day (so that part can be left out if doable), I just need to format the output to the SPList as ISO 8601 matching format.

if(equals(items('Apply_to_each')?['Compose'],null),null,addDays(items('Apply_to_each')?['Compose'],0))

The flow error is - "addDays", the value provided for date time string was not valid. The datetime string must match ISO 8601 format.

When I run the flow all lines that have dates in both the 'HireDate' and 'TempStartDate' populate fine; Only the lines with no 'TempStartDate' are missing and initiate the flow error.

Categories:

Building flows

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

MarcelLehmann 378 on at

Like (1)

Report
Copy link

Link copied!
To work around this issue, you can modify the expression in the date fields of the 'Create item' action to first check if the value is null or blank before trying to convert it to a date format.

Here's an example expression that you can use:
if(empty(items('Apply_to_each')?['Compose']), null, formatDateTime(items('Apply_to_each')?['Compose'], 'yyyy-MM-ddThh:mm:ssZ'))

Was this reply helpful? Yes No
Phineas 5,331 Moderator on at

Like (0)

Report
Copy link

Link copied!

Edit:

Magic!

I ran the test on 15 items.

All came through.

In the source date 3 of the items had both dates.

All of the items where 'TempStartDates' was blank came through.

However, none of the items that actually HAD dates showed dates.

I got 15 items; Three of them should have had 'TempStartDates', and didn't.

The code -
if(empty(items('Apply_to_each')?['Compose_2']),null,
formatDateTime(items('Apply_to_each')?['Compose_2],
'yyyy-MM-ddTHH:mm:ssZ'))

Was this reply helpful? Yes No