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

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.🙄

Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!