Mar 01 2017 02:10 PM
I am exporting data from a database.
Column B contains a date/time and looks like this: 01/01/2017 07:10:59. If you select column B, the column type is "text."
I use the "text to columns feature," set the column as fixed width, and select Date as the column type for the date column. This results in column B conatining 01/01/2017 and column C containing 07:10:59. If you select column B now, Excel claims that this is a Date.
In column A I want the Day of the Week to appear.
In cell A1 I type: =text(B1,"ddd") and I get exactly that string of text in the cell, not the day of the week.
Now for the real kicker.
If I go to the bottom of column B (say B500) and type in a date such as 03/01/2017 and put the same formula in column A (A500) - I get what I want which is the Day of the Week.
Any ideas?
This used to work. I have Office Professional 2010, upgrading is not an option.
Mar 01 2017 03:36 PM - edited Mar 01 2017 03:36 PM
Hi RJ
If your date is in cell B1 then try this =TEXT(DATEVALUE(B1),"ddd")
Mar 02 2017 09:42 AM
Thank you, Wyn but this does not work either.
I feel like there is some sort of hidden formatting or switch that has been toggled when the file is exported from the database. Like I said, sometimes my first attempt and your suggestion do work.
Mar 02 2017 11:07 AM
Hi RJ,
Is "dd/mm/yyyy" your regional settings format for the dates?
Feb 19 2019 12:30 PM
Feb 19 2019 01:37 PM
Sep 17 2019 09:08 PM
The problem is probably a conflict between the general system date settings and the excel date settings. I don't know how to change the excel date settings (and changing the date in the cells doesn't result in anything). However if you change the general system date settings (in my case Windows 10) to a different country format (in my case UK) then suddenly the formula works and you can say the name of the day instead of the "ddd".
May 20 2022 08:16 AM
An old thread, I know but I figured it out. You need to adapt the formula to your language. My Excel is french, so I need to use "jjj" for jour instead of "ddd" for day.
When looking at Format Cells-> Custom you will see which letter your language-specific version of Excel is using. In french it uses aaaa-mm-jj instead of yyyy-mm-dd.
Jul 20 2023 01:00 AM
Hi @Tony20z220
I'm using 'ddd' in my formula but the name of the days are coming in Spanish. My computer language is English though. Do you know how can I make them come in English?
Thanks!
Jul 20 2023 07:02 AM
If the formula works, but the result is in the wrong language, I would start checking language settings in Excel or Windows. They may be set to english but have a different region so excel is using the region to decide the language for the formula. That would be my guess.
Jul 20 2023 09:31 AM
Excel uses regional settings to recognize dates and current language to perform operations on it by formula.