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.
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
vinaya ,
- It's not necessary to wrap date parts by VALUE()
- If transform, Text to Columns works fine
It's here. Depends on how do you enter dates. If for 02 April 2024 as 02/04/2024, when setting is dd/mm/yyyy. If above date is added as 2024-04-02, when the setting is yyyy-mm-dd.
- 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
