Home

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

%3CLINGO-SUB%20id%3D%22lingo-sub-49666%22%20slang%3D%22en-US%22%3EFunction%20%3Dtext(cell%2C%22ddd%22)%20fails%20to%20convert%20a%20date%20to%20a%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-49666%22%20slang%3D%22en-US%22%3E%3CP%3EI%20am%20exporting%20data%20from%20a%20database.%3C%2FP%3E%3CP%3EColumn%20B%20contains%20a%20date%2Ftime%20and%20looks%20like%20this%3A%2001%2F01%2F2017%2007%3A10%3A59.%20If%20you%20select%20column%20B%2C%20the%20column%20type%20is%20%22text.%22%3C%2FP%3E%3CP%3EI%20use%20the%20%22text%20to%20columns%20feature%2C%22%20set%20the%20column%20as%20fixed%20width%2C%20and%20select%20Date%20as%20the%20column%20type%20for%20the%20date%20column.%20This%20results%20in%20column%20B%20conatining%2001%2F01%2F2017%20and%20column%20C%20containing%2007%3A10%3A59.%20If%20you%20select%20column%20B%20now%2C%20Excel%20claims%20that%20this%20is%20a%20Date.%3C%2FP%3E%3CP%3EIn%20column%20A%20I%20want%20the%20Day%20of%20the%20Week%20to%20appear.%3C%2FP%3E%3CP%3EIn%20cell%20A1%20I%20type%3A%20%26nbsp%3B%3Dtext(B1%2C%22ddd%22)%20and%20I%20get%20exactly%20that%20string%20of%20text%20in%20the%20cell%2C%20not%20the%20day%20of%20the%20week.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20for%20the%20real%20kicker.%3C%2FP%3E%3CP%3EIf%20I%20go%20to%20the%20bottom%20of%20column%20B%20(say%20B500)%20and%20type%20in%20a%20date%20such%20as%2003%2F01%2F2017%20and%20put%20the%20same%20formula%20in%20column%20A%20(A500)%20-%20I%20get%20what%20I%20want%20which%20is%20the%20Day%20of%20the%20Week.%3C%2FP%3E%3CP%3EAny%20ideas%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20used%20to%20work.%20I%20have%20Office%20Professional%202010%2C%20upgrading%20is%20not%20an%20option.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-49666%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-860392%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20%3Dtext(cell%2C%22ddd%22)%20fails%20to%20convert%20a%20date%20to%20a%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-860392%22%20slang%3D%22en-US%22%3E%3CP%3EThe%20problem%20is%20probably%20a%20conflict%20between%20the%20general%20system%20date%20settings%20and%20the%20excel%20date%20settings.%20I%20don't%20know%20how%20to%20change%20the%20excel%20date%20settings%20(and%20changing%20the%20date%20in%20the%20cells%20doesn't%20result%20in%20anything).%20However%20if%20you%20change%20the%20general%20system%20date%20settings%20(in%20my%20case%20Windows%2010)%20to%20a%20different%20country%20format%20(in%20my%20case%20UK)%20then%20suddenly%20the%20formula%20works%20and%20you%20can%20say%20the%20name%20of%20the%20day%20instead%20of%20the%20%22ddd%22.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F41865%22%20target%3D%22_blank%22%3E%40RJ%20Johnson%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353596%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20%3Dtext(cell%2C%22ddd%22)%20fails%20to%20convert%20a%20date%20to%20a%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353596%22%20slang%3D%22en-US%22%3EHi%2C%20could%20you%20start%20a%20new%20post%20with%20your%20question%20%3CBR%20%2F%3E%3CBR%20%2F%3EMore%20chance%20of%20getting%20a%20response%20and%20it%E2%80%99s%20best%20to%20avoid%20mixing%20questions%20%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-353566%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20%3Dtext(cell%2C%22ddd%22)%20fails%20to%20convert%20a%20date%20to%20a%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-353566%22%20slang%3D%22en-US%22%3ESi%20am%20having%20an%20issue%20that%20sims%20to%20be%20in%20this%20ball%20park%20if%20it%20is%20not%20please%20direct%20to%20where%20i%20should%20go.%3CBR%20%2F%3E%3CBR%20%2F%3EI%20have%20been%20working%20on%20a%20file%20where%20i%20take%20weight%20x%20ft%20x%203%20%3D%20formula%20on%20export%20i%20get%20the%20formula%20expression%20instead%20of%20the%20actual%20formula%20result.%3CBR%20%2F%3E%3CBR%20%2F%3EHow%20can%20i%20export%20as%20the%20result%20instead%20of%20the%20formula%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-49890%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20%3Dtext(cell%2C%22ddd%22)%20fails%20to%20convert%20a%20date%20to%20a%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-49890%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20RJ%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20%22dd%2Fmm%2Fyyyy%22%20your%20regional%20settings%20format%20for%20the%20dates%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-49862%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20%3Dtext(cell%2C%22ddd%22)%20fails%20to%20convert%20a%20date%20to%20a%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-49862%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%2C%20Wyn%20but%20this%20does%20not%20work%20either.%3C%2FP%3E%3CP%3EI%20feel%20like%20there%20is%20some%20sort%20of%20hidden%20formatting%20or%20switch%20that%20has%20been%20toggled%20when%20the%20file%20is%20exported%20from%20the%20database.%20Like%20I%20said%2C%20sometimes%20my%20first%20attempt%20and%20your%20suggestion%20do%20work.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-49685%22%20slang%3D%22en-US%22%3ERe%3A%20Function%20%3Dtext(cell%2C%22ddd%22)%20fails%20to%20convert%20a%20date%20to%20a%20day%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-49685%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20RJ%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20your%20date%20is%20in%20cell%20B1%20%26nbsp%3B%20then%20try%20this%20%26nbsp%3B%26nbsp%3B%3DTEXT(DATEVALUE(B1)%2C%22ddd%22)%3C%2FP%3E%3C%2FLINGO-BODY%3E
RJ Johnson
New 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. 

 

 

6 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 

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
12 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies