Changing cell styles in Numbers on Mac

Question

Level 1

4 points

Changing cell styles in Numbers on Mac

I’m having a problem changing a cell style in Numbers on a Mac. The style is automatic or can be changed to text. It is of the format 3/3/25

I expected to be able to change this is a Date format from what must be pure text.

I know you should be able to do this in the format cell menu but when you do this it just reverts to Automatic.

The weird thing is that some cells are okay but most are just text and won’t change.

The source of the file was an XLSX (Excel). However also checked and I have this issue if I read in the equivalent CSV file.

Interestingly using a completely different CSV file (that came from a different source) it works just fine so it must be something specific about the data in the cells but as they are text I can’t figure out what the issue is.

I was just trying to move from Excel to numbers but I’m stuck on this issue.

I need to be able to compare a set of dates to check currency rates on a particular date and of course that won’t work if you compare dates with text.

Hope you can help me out.

MacBook Air, macOS 15.5

Posted on Jul 26, 2025 12:20 PM

Reply

Answer 1

Top-ranking reply

SGIII

Level 8

37,532 points

Jul 27, 2025 8:14 AM in response to RobG-UK

RobG-UK wrote:

it’s actually a mix. In one sheet it’s US date format and in another sheet it’s UK date format. This is just because of the separate sources for the original data.

For Numbers to recognize the dates correctly you need to have your app set to a region that uses the same date format as the data source.

If the source uses UK date format then before importing the data go to File > Advanced > Language and Region and make sure you have chosen a region that uses that format.

If the source uses US date format then before importing the data go to File > Advanced > Language and Region and make sure you have set the region to United States.

You can switch region, import, then switch back (if necessary) to your preferred region.

If you are having trouble switching the region at File > Advanced > Language and Region (e.g., the dropdown is grayed out) then explicitly choose a language first (even if the system default language is correct).

SG

Reply

Answer 2

RobG-UK Author

Level 1

4 points

Jul 27, 2025 9:33 AM in response to SGIII

Thanks, yes this is the problem. When importing from the CSV it leaves as text any date with a day exceeding 12 (as it assumes the 2nd digit is a month. Interestingly when I imported the XLSX file into Numbers this creates a custom cell format “Custom Format 1” that has the correct month/day/year format. So I assume Excel figures out that it must be US format (even on a UK PC). However the dates are read into Numbers as if they were UK format so the custom format is only applied to dates that have a day value of 12 or less and strangely for the other dates the text can’t be changed from a text style to this custom style once imported into Numbers.

These cells that use the custom format correctly match the UK formatted dates in the UK formatted sheet if 6/12/24 matches 12th June 24.

so I’ll try setting the laptop to be in US region and import the fil and then hopefully cut and paste those dates into my modified version of the file.

if this is expected behaviour on import than I assume it’s not considered a bug. However once imported and the cell is just text isn’t it a bug that I can’t change the format to be a custom date time to accommodate the US date format. It can’t be changed from text or Automatic.

Reply

Answer 3

SGIII

Level 8

37,532 points

Jul 26, 2025 1:53 PM in response to RobG-UK

If Excel displays the date correctly have you tried copy-pasting into a Numbers table rather than opening in Numbers?

Is the date format in the source the same as the date format in the region your Mac is set to?

Does 05/01/25 mean May 1, 2025 or January 5, 2025?

Problems often arise when the source data uses one format but your Mac expects the other.

If the above doesn't help you solve the mystery, then you might want to post a screenshot of what you are seeing (shift-command-4, select area, release, start new post here and use the mountains-and-moon 'Image insertion' icon to insert the screenshot from the Desktop) and also a small sample of the problematic CSV data.

SG

Reply

Answer 4

RobG-UK Author

Level 1

4 points

Jul 27, 2025 1:01 PM in response to RobG-UK

Many thanks, that solved the problem. I set the country to USA although I can’t be sure of the exact steps. If I set the country then open a CSV it opens a new file with this data so I think the country was back to UK. The data was still considered to be in UK format but it allowed me to create a custom format to interpret the cells as US date format and then all was fine.

Thanks for help.

Reply

Answer 5

RobG-UK Author

Level 1

4 points

Jul 27, 2025 2:38 AM in response to SGIII

Thanks for the feedback.on the dates it’s actually a mix. In one sheet it’s US date format and in another sheet it’s UK date format. This is just because of the separate sources for the original data.

Regarding cutting and pasting. I don’t have access to Excel on the Mac so I haven’t tried cutting pasting between Excel and Numbers. However the problem occurs whether I open the original CSV or XLSX files with Numbers. I just had the Excel file as I was previously on a Windows PC and am trying to switch to just using Mac.

However for the few cells that are in date format (not just text) comparing the US and UK date formats between sheets works fine. So the issue is why the data is fixed as text in the majority of the US format date cells which are all in a single column.

I’ll check on the PC whether the Excel file also has the dates as text or not but I’ve not experienced this problem before with Excel.

Reply

Answer 6

RobG-UK Author

Level 1

4 points

Jul 26, 2025 12:45 PM in response to RobG-UK

Just an update. Since some date cells are okay I can copy and paste the cell and style to all the cells. This works fine but of the dates are all the same so incorrect.

Then I thought I might be able to paste the cells by copying from the original CSV file. However when I use paste and match style I get the same problem. Some are fine but most have reverted to text.

When I’m trying to change all the cells to date and time the format cell menu shows I have 2 date formats 05/01/25 and 05/01/2025 and two times None and 19:08 (is that just a default?)

I guess I’ll try looking at the CSV file in a text editor to see if there’s anything weird that’s causing the issue.

Reply