Forum Discussion
Solved
Excel Date Formatting
Hello All,
I am looking to better understand why Date formatting never seems to work. I am looking to format to day/month/year in a numerical format. (00/00/0000) No matter what I do excel formats back to the US format list, which doesn't have this format.
I am not providing a specific example because I am hoping someone will actually put a complete and final solution so I never have scroll a forum on this subject again. I am sure some people here can relate.
I have cleared formats, changed formats, etc. I have changed the country formatting to Canadian so it is on the list. I have gone through the defaults. I have ensured its not formatted as text, etc.
Of everything found on here, and the only thing that comes close to working is custom formatting, which defeats the point.
Any thoughts? It's always a pain with Microsoft programs (as a whole, no matter which computer I use, no matter business or personal) as they relentlessly default to American options, no matter what defaults I put in. Can this be defined directly in the OS to prevent recurring issues? Can US language formatting be disabled?
Thanks,
Wes
What are your regional settings? Excel takes them from OS. For Windows that's here
As a comment to
I put in the data as day/month/year it converts it to month/day/year, without fail
it again depends on regional settings. If you have US ones and enter Dec 24, 2023 as 24/12/2023 it won't be converted to date, it will be kept as text.
Again, forget about formatting for a while, check first your regional settings and do you have real dates, not texts. You may use =ISNUMBER(A1) or like in any empty cell.
7 Replies
Main question is do the speak about the dates (which are actually numbers) or about the texts which looks like dates. The latest are usually left-aligned
and applying any format to such cells changes nothing.
Thanks for your very quick response.
My issue is that when I put in the data as day/month/year it converts it to month/day/year, without fail.
Here is a list of things I have tried. Between each of this it can be assumed I cleared all the formats and tried again.
- Formatting the cells as day/month/year before inputting the data
- Inputting the data and using the format function on the cells.
- Inputting the data by copy and paste (using destination format) from another set of cells that were formatted as text.
- And many others.
Basically it has become a magic trick. Honestly, I just made it work before your message and it was so convoluted getting to it I am definitely unsure as to what I did.
Thanks,
Wes
What are your regional settings? Excel takes them from OS. For Windows that's here
As a comment to
I put in the data as day/month/year it converts it to month/day/year, without fail
it again depends on regional settings. If you have US ones and enter Dec 24, 2023 as 24/12/2023 it won't be converted to date, it will be kept as text.
Again, forget about formatting for a while, check first your regional settings and do you have real dates, not texts. You may use =ISNUMBER(A1) or like in any empty cell.
