Forum Discussion
Excel Date Formatting
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.
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.
Hi, I tried following the steps below but unfortunately it still won't work on my end... Can you please elaborate for me as I seem to be not getting it (?) or did I miss anything???... Is the Region setting in the Control panel the only thing I have to change? or do I still have to look elsewhere? Thank you so much for the help in advance!
Hi Gaille_24 and Community,
I recently encountered an issue with dates stored as text in the format MM/DD/YYYY, and Excel functions like DATEVALUE or direct conversions weren’t working. I’d like to share a solution that worked well for me — it might help others facing a similar issue.
Steps I followed
- Sorted the column containing the text dates to ensure consistent formatting.
- Extracted date components using the following formulas:
- =VALUE(LEFT(T4,2)) --------- Extract Month
- =VALUE(MID(T4,4,2)) -------Extract Day
- =VALUE(RIGHT(T4,4)) ------ Extract Month=DATE(R4,P4,Q4) ------ Combined all 3 with Date Function
Where: T4 is the original cell with the text-formatted date. P4, Q4, and R4 store Month, Day, and Year respectively.
This method is particularly useful when Excel doesn't automatically recognize text dates, especially during data imports.
It’s a simple fix, but it saved me time — hope it’s useful for others too. Appreciate any other suggestions or improvements from the community as well!
Best regards,
Vinaya G
- I looked and for whatever reason the regional format was english (canada) but the region/country was United States.
I appreciate the assist. This seems to have fixed it, as far as my current tests go.
That being said considering that every other option was selected as English (Canada)... I am not sure how a single option can change every other option. Very confusing.
Thanks again.Wes12345 , you are welcome
