Template Errors for Date and Time from Excel table to SharePoint List

[ccolletti]

Hi all, I currently have a flow that takes data from an Excel Table and loads it into a SharePoint List. The goal for this list is to track appointments for my company, so we have a column set with the Date and Time format. The results I'm looking for is 2/22/2022 11:00AM.

I've attempted to upload the data coming in the table without the time using a created variable and the expression: 

addDays('1899-12-30',int(variables('EventDate')),'MM-dd-yyyy'). however this came in as null so no data uploaded into the list.

I've done research on other ways to import the data, but still have not had any success. I've tried the Compose action with the expression 

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Time']),-2)), 'yyyy-MM-dd'), as well as just entering this expression into the Event Date field. Error message I'm getting with the failed run is:

Recently I've tried the formula, formatDateTime('<your-value>', 'dd/MM/yyyy hh:mm tt'), but I've been running into the following errors:

I'm hoping that maybe someone has another route I could take to get the desired results in my list. Thank you so much in advance for your help!

[abm]

Hi @ccolletti 

Could you post a screenshot of your Excel action step and sample data?

Thanks

[ccolletti]

@abm Here's the screenshots, just let me know if I'm missing anything.

Test Data:

Excel Actions:

These actions are back to back in their own Apply to Each. I did notice the DateTime Format was not ISO 8601 originally, so I changed it over to that and tested, however that caused the Create Item action to skip. Looking into the run, my Get Item action that is dependent on the store number column came back as null (this communicates with another list to pull in store information), but the date field showed the correct format in the output. So I need to figure out how to convert the date.

[abm]

Hi @ccolletti 

Thanks for your reply. Did you checked the run history. What format the Excel DateTime is getting from the Excel action step?

[ccolletti]

@abm 

The original run came in as a number:

But the latest test did come in as a date:

[abm]

Hi @ccolletti 

Thanks for your reply. Is that resolved your issue?

[ccolletti]

@abm no, that actually didn't resolve the issue, I'm not getting any data to upload. Not sure if this had anything to do with the store number after I changed to ISO 8601. But nothing loaded up into my list from the test, just skipped the Create Item action, so I changed it back. 

Didn't know if there's a formula I can use to convert the date with time from the number to then load into the Date and Time formatted column in my list. I've attempted a few that I noted above, however those have not worked. 

[abm]

Hi @ccolletti 

What do you mean by its skipped or are you getting any run errors?

Thanks

[ccolletti]

@abm 

This is what I mean by skipped. The flow would run, but no information would go into the list because the Create Item didn't run. 

Error messages I had been getting I put in the original post: 

I've attempted to upload the data coming in the table without the time using a created variable and the expression: 

addDays('1899-12-30',int(variables('EventDate')),'MM-dd-yyyy'). however this came in as null so no data uploaded into the list.

I've done research on other ways to import the data, but still have not had any success. I've tried the Compose action with the expression 

formatDateTime(addDays('1900-01-01', add(int(items('Apply_to_each')?['Time']),-2)), 'yyyy-MM-dd'), as well as just entering this expression into the Event Date field. Error message I'm getting with the failed run is:

Recently I've tried the formula, formatDateTime('<your-value>', 'dd/MM/yyyy hh:mm tt'), but I've been running into the following errors:

[abm]

Hi @ccolletti 

Whats the previous step? Looks like previous step not retrieving any data.

[ccolletti]

@abm 

The step before is Get Item, which communicates with another list using a variable for the store number. This pulls in the store information for the leads that are to be loaded up from the trigger excel file.

Note: the Title value is variables('StoreNumber')

[abm]

Hi @ccolletti 

Could you also try the format DateTime with the below format

yyyy-MM-ddTHH:mm:ssZ

[ccolletti]

@abm I just tried with 

formatDateTime(variables('EventDate'), 'yyyy-MM-ddTHH:mm:ssZ') and it still came up with the incorrect time, unfortunately. Coming in as 3:00 AM and not 11:00 AM, like when we were testing this yesterday.

[abm]

Hi @ccolletti 

What was the date? I will also test this and let you know.

Thanks

[ccolletti]

the date in the test was 2/26/2022 11:00 AM, but you can use any date. Thanks

[abm]

Hi @ccolletti 

I tested it and it has shown the right date in SharePoint. The only difference was my Excel sheet date I set to American format and my SharePoint settings where UK Date settings. Flow ran successfully and uploaded the right date in SharePoint. SharePoint displayed the date in UK format. I believe you having issues with your SharePoint settings. Do check with your admin about regional settings.

Thanks

[ccolletti]

@abm thanks for the follow up. Yes, I've reached out to the admin and I'm just waiting on a response.