Excel to Sharepoint DateTime not correct and dates after the 13th not added

[Rosie]

Hello,

Apologies for yet another question on this topic but I can't understand how to resolve this.

I have an excel spreadsheet with a date column and 2 dateTime columns.

I want to add these to a sharepoint list in the format dd/mm/yyyy hh:mm tt

The site settings are, TimeZone: (UTC+ 12:00) Auckland, Wellington.  Locale: English (New Zealand)

The flow's List Rows present in table is set to datetime format ISO 8601

Excel data

SharePoint List output

No records past the 13/4/2020 are added - assuming the system thinks this can't exist based on US date - the the 13 being a month but in this case it is the day.

The error is: The runtime value '"13/04/2020 12:30"' to be converted doesn't have the expected format 'String/date-time'.

The dates time in the flow looks correct.

The Apply to each part of the flow is here:

Thanks

Rosie

[v-xiaochen-msft]

Hi @Rosie ,

The best way is to convert the datetime(dd/MM/yyyy) to a general format(yyyy/MM/dd) because if the year is in the end of the format, sharepoint will parse it as MM/dd/yyyy despite its actual format is dd/MM/yyyy. This is the expected behavior.

Please try this function:

https://learn.microsoft.com/en-us/azure/logic-apps/workflow-definition-language-functions-reference#...

formatDateTime(parseDateTime(outputs('Compose'),'en-us','dd/MM/yyyy'),'yyyy-MM-dd')

Best Regards,

Wearsky

[Rosie]

Hello,

Thanks for you help, I am still having issues with this.

I also need the time, so I have added that in which might be the issue.

formatDateTime(parseDateTime(variables('startDate'), 'en-us', 'dd/MM/yyyy hh:mm tt'),'yyyy-MM-dd hh:mm')

I get the error:

Unable to process template language expressions in action 'Create_item' inputs at line '0' and column '0': 'In function 'parseDateTime', the value provided for date time string '01/04/2020 00:30' was not valid. The datetime string must match ISO 8601 format.'.

Thanks

Rosie

[wskinnermctc]

I don't know why it is having trouble getting the time value from Excel. Are you datetime columns in Excel text? 

I just tested it with the dates formatted like yours, and I didn't have any issue with power automate not getting the date correct.

You could try my solution in this post. See if you can use the Convert Time Zone actions to simplify some of this.

https://powerusers.microsoft.com/t5/General-Power-Automate/Adjust-DateTime-values-from-Excel-to-a-UT... 

[Rosie]

The date time columns are in date.  Should they be in text?

I have tried your solution but it also doesn't work, the dates converted are incorrect and it fails when it gets to the 13/4/2020 (13th April 2020).

[wskinnermctc]

That time is correct

2020-01-04T00:30 New Zealand Standard Time = January 4, 2020 12:30 AM

If that is converted to UTC then it will be January 3, 2020 11:30 AM

[Rosie]

I was following your solution.

I am confused.  Do I just need to convert it from NZ to NZ in the 2nd conversion?

https://powerusers.microsoft.com/t5/General-Power-Automate/Adjust-DateTime-values-from-Excel-to-a-UT...

[Rosie]

If I convert from NZ to NZ I end up with it 1 hour added.

When it gets to 13/4/2020 it fails as usual.

[wskinnermctc · Accepted solution]

I made an example and going to show all of the data and flow steps. When I compare my results with what you are showing, I see a difference where the excel date is initially being read by power automate.

I don't think power automate is recognizing your excel date as a DateTime, but instead reading it as a string. 

Best way is to compare the initial output with nothing being changed. Your set variable steps are the same as my A0 Compose step, because it is just the date being put into an action and we can see the output.

• Your example: The variable output is "11/04/2020 01:15" which is a string, not a date time. 
• My example: The compose output is "2020-04-11T01:15:00.000Z" which is a UTC datetime.

Power automate will automatically convert an excel datetime into UTC if the ISO8601 option is selected in the List Rows action.

Your values aren't even being recognized as a date time from the beginning. They just happen to work when going into sharepoint as strings but they aren't really being recognized as datetime. Which is why the time is still incorrect on the ones that work, and when the string isn't in easily readable format for SharePoint, it errors out.

Do this:

• Run the flow
• Go to view the flow results/outputs
• Go to the List Row present in a table
• Click the Outputs "Click to download"
  • It should show a new window with a lot of raw code that contains all of the excel data
• Search/Find through the raw code until you can recognize the Excel columns that have your start and end dates
  • It will be something like "Start DT":"some value","End DT":"some value"

Whatever is showing in the "some value" is how power automate is reading the data. If it says something like "End DT":"43932.041666" then that means it is being read as serial. If it says something like "End DT":"2020-04-11T01:15:00.000Z" then it is being read as ISO 8601. If it says "End DT":"11/04/2020 01:15" then it is being read as Text.

Test this with the List Rows Present in Table option DateTime Format as IS0 8601 and then test it with the option Serial Number. Compare the outputs. If power automate is recognizing the excel "End DT" column as a datetime field, then the outputs should change between ISO 8601 and Serial Number.

Let me know how the results turn out

[v-xiaochen-msft · Accepted solution]

Hi @Rosie ,

Please remove the 'tt'.

Best Regards,

Wearsky

[Rosie]

Hello,

Thanks very much for the explanation.

They are in this format:

So it must be interpreting this as a string.

So does that mean I have to split the day, month, year and time into separate fields and then put them back together again.

Thanks

Rosie

[wskinnermctc]

Yes, it is a string. You will have to split the data and put it into a format that you want. 

You could also try changing the option to Serial and see if you can use it like that. It might be easier to use Serial conversion expressions than a lot of splitting and reformatting.

You would have to test and see if it is recognized as Serial or if it remains a text string even after selecting the Serial option. 

[Rosie]

Hi, Removing the tt wasn't the solution to the issue in my case, thanks for your help.

Rosie