Cannot get date to show correctly after upload of spreadsheet (xlsx)

Katrina Walker

I upload most of my data via data shuttle as they are imports from other systems used in our corp. I have an excel sheet which i upload and has dates in the format of date/time and I only want the date to use in metrics sheets I have created.

I have tried using Left and DateOnly functions in the next cell to convert to a date. Left makes it look okay but my metrics sheet does not see it as a date and with DateOnly I get # INVALID DATA TYPE even though both cells as marked as date cells.

The only way I have managed to get it to work is to change the column with the date/time in it to text/number then back to date and the cell updates to only the date and the formula in my metrics sheet works again. Obviously this is not very automatic.......

The spreadsheet is saved as xlsx and that is automated from email through power automate into my OneDrive. I could save as csv but not sure that would make any difference. I have other sheets that I upload daily into SmartSheet but those have dates not date/time which i think is the problem here.

Any ideas appreciated.🙄

Paul Newcome

Try this...

=DATE(VALUE(MID([Incident Start Date Time]@row, 7, 4)), VALUE(MID([Incident Start Date Time]@row, 4, 2)), VALUE(LEFT([Incident Start Date Time]@row, 2)))

Paul Newcome

Try this...

=DATE(VALUE(MID([Incident Start Date Time]@row, 7, 4)), VALUE(MID([Incident Start Date Time]@row, 4, 2)), VALUE(LEFT([Incident Start Date Time]@row, 2)))

Katrina Walker

This works perfectly! Thank you so much for your help Paul, much appreciated.

Paul Newcome

Happy to help. 👍️