Excel file date instead of number

Occasional Visitor

Hello,

 

In this file coming from Uber eats, I should see numbers but instead i see dates or invalid value. 

how can I set up my excel to read correctly this type of file ? 

 

I imported on google sheet: https://docs.google.com/spreadsheets/d/1FEdmbvzIMlV74bdy8fkDDq2NpxGQaR8vsP23Uqzg95w/edit?usp=sharing  (a bit better than on my mac excel software) but still for example colum I line 7 you ca see it's a date ...

 

Capture d’écran 2021-12-21 à 12.50.04.png

2 Replies

@pedrodu69 Did you by any chance open a CSV file by directly clicking on it, or opening it in Excel? If so, try to import the file via the Data Ribbon. Get Data, From Text (Legacy) and follow the instructions. In step 3 of 3, make sure to mark all columns creating problems as Text. That will preserve them as texts. Otherwise a "number" like 5.46 may be interpreted as May 1946, just like in your screenshot.

Screenshot 2021-12-21 at 13.03.07.png

@pedrodu69 

Apparently you use comma as decimal separator and point (dot, period) as date separator.

The file uses point as decimal separator. This causes the confusion.

 

You can set Excel to use point as decimal separator:

  • Select File > Options.
  • Select Advanced.
  • Clear the check box 'Use system separators', then specify point as decimal separator and comma as thousands separator.
  • Click OK.

S1009.png

See if that works better.

You can tick 'Use system separators' again afterwards.