Excel serial number conversion

(0) Share

Report

Posted on by ccqn

205

Hi all,
I am trying to populate a sharepoint list using entries from Excel but am running into errors when the date column in Excel is blank.

In the list rows in Excel step, I have set the datetime format to be ISO8601 but it is still getting read as a serial number so I have been trying to use the follow formula:

if(empty(items('For_each')['Date Joined']), null, addDays('1899-12-30', int(items('For_each')['Date Joined'])))

but get the error "Error details: Input parameter 'item/field_45' is required to be of type 'String/date'. The runtime value '""' to be converted doesn't have the expected format 'String/date'. when the Date Joined value is blank. I have used a Compose statement to ensure that it is returning null but it just doesn't seem to work. Any help would be very much appreciated.

All responses (1)

Answers (1)

Verified answer

v-yetonggu-msft on at

Like (0)

Report
Re: Excel serial number conversion

Hi @ccqn ,

I did a test for your reference.

In my scenario:

Please switch to classic designer.

My Excel Table:

I'm using the 'Apply to each' loop instead of 'For each'.

When you quote my expression, remember to change the name of the 'Apply to each loop' to 'For each'.

My Flow no ISO 8601 Format:

if(empty(items('Apply_to_each')['Date']), null, addDays('1899-12-30', int(items('Apply_to_each')['Date']),'yyyy-MM-dd')) utcNow('yyyy-MM-dd')

With ISO 8601 Format:

if(empty(items('Apply_to_each')['Date']), null, formatDateTime(items('Apply_to_each')?['Date'],'yyyy-MM-dd')) utcNow('yyyy-MM-dd')

Best Regards,

Sunshine Gu