Time formatting error, produces wrong date and time

[Ulli]

Hi there,

I am getting errors when creating csv from excel.

I have the following columns (Subject, Start Date, Start Time, End Date, End Time, All Day Event, Description, Location, Private) and it does not like when the end date changes (+1) and interprets end time, example: 2am, as ‘1900-01-01T02:00’ in the End time Column. When Start and End date are the same it works and indicates the time only, and correctly. I only want the time showing. The columns need to be in this order for google calendar to import. Please see attached saved as xlsx.
I have tried string type and local time type and the result is the same. It also gives an error message for just the rows where start and end date differ:
Row with ID ‘Row102’ can’t be converted to the configured data types. Increasing the number of scanned rows or changing the target types might resolve the issue.*

It also sometimes says on the Settings tab of excel reader
Row 101: java.lang.reflect.InvocationTargetReflection

Console says
ERROR Excel Reader 0:1182 Execute failed: Row with ID ‘Row102’ can’t be converted to the configured data types [local time]. Increasing the number of scanned rows or changing the target types might resolve the issue.

The suggested solutions don’t work, unfortunately.

Any suggestions would be welcome

Thank you
To Dos new.csvExport_230107-1.xlsx (10.8 KB)

[ArjenEX]

HI @Ulli

I would import the Excel as is (under Advanced Settings, uncheck Limit data rows scanned). The sample that you gave results in the End Time being of type string. If you then take the most 5 right characters, you will have a proper time indication. Expression: right(column("End Time"),5)

Next, convert it to Local Time with a String to Date&Time node.

[Ulli]

Thank you @ArjenEX ,

It sounds straight forward and the example for the first row it gives is accurate BUT unfortunately it does not work for the row where I get the text ‘1900-01-01T01:00’.
I get this error message
ERROR String to Date&Time 0:1184 Execute failed: Failed to parse date in row 'Row100: Text ‘1900-01-01T01:00’ could not be parsed at index 2.

I think SQL read from excel that I had a time calculation going on that jumped across midnight and therefore included a date and flagged up the error, ie for rows where indeed the time window went across midnight it ‘knew’ this is not just ‘time’, it’s also linked to a date and did not allow just a time format.

I worked around it in the excel source sheet and created a column to extract time only (even though there was only time showing) to get rid of any background noise that could trigger sql errors.
=TIME(HOUR(A1),MINUTE(A1)
This gave me clean data that gave no further issues

Thanks again, I really appreciate it

Ulli