Function =text(cell,"ddd") fails to convert a date to a day

Copper Contributor

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. 

 

 

10 Replies

Hi RJ

 

If your date is in cell B1   then try this   =TEXT(DATEVALUE(B1),"ddd")

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.

Hi RJ,

 

Is "dd/mm/yyyy" your regional settings format for the dates?

Si am having an issue that sims to be in this ball park if it is not please direct to where i should go.

I have been working on a file where i take weight x ft x 3 = formula on export i get the formula expression instead of the actual formula result.

How can i export as the result instead of the formula
Hi, could you start a new post with your question

More chance of getting a response and it’s best to avoid mixing questions

Thanks

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". 

 

@RJ Johnson 

@RJ Johnson 

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.

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!

@fmolinares 

 

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.


@Tony20z220 

Excel uses regional settings to recognize dates and current language to perform operations on it by formula.