Home

date conversion

%3CLINGO-SUB%20id%3D%22lingo-sub-794990%22%20slang%3D%22en-US%22%3Edate%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-794990%22%20slang%3D%22en-US%22%3E%3CP%3Etrying%20to%20alter%20date%20in%20date%20format%20to%20calendar%20%2F%20time%20or%20text%20format%26nbsp%3B%20ie.%20nos.%20to%20words%3C%2FP%3E%3CP%3Ethis%20is%20the%20area%26nbsp%3B%20below%20%3A%26nbsp%3B%26nbsp%3B%20the%20formula%20i%20used%20is%20%3DTEXT(A62%2C%22dddd%22)%20in%202nd%20cell%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E01.10.99%3C%2FTD%3E%3CTD%3E01.10.99%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eand%20customised%20in%20cell%20format%20as%26nbsp%3B%20text%20%2C%26nbsp%3B%20custom%3C%2FP%3E%3CP%3Ewh%20y%26nbsp%3B%20wont%20it%20work%20%3F%26nbsp%3B%20i%20want%20to%20display%20the%20day%20in%20words..%3C%2FP%3E%3CP%3Ealso%2C%26nbsp%3B%20the%20sheet%20and%20workbook%20protect%20function%20doesnt%20seem%20to%20work%3C%2FP%3E%3CP%3Eim%20using%20office%20365%20trial%20version%20%2C%2064%20bit%20i%20think..%3C%2FP%3E%3CP%3Ethanks%3C%2FP%3E%3CP%3Ec....%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-794990%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-795514%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-795514%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384239%22%20target%3D%22_blank%22%3E%40calgar1700%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhat%20exactly%20would%20you%20like%20to%20display%20in%20the%20second%20cell%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796070%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796070%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384239%22%20target%3D%22_blank%22%3E%40calgar1700%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAre%20you%20sure%20you%20have%20date%2C%20not%20text%2C%20in%20first%20cell%3F%20You%20may%20check%20by%20ISTEXT()%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796921%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796921%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F360420%22%20target%3D%22_blank%22%3E%40PReagan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ewish%20to%20show%20eg%20monday%20Ist%20october%201999%3C%2FP%3E%3CP%3Eie.%20words%20format%20not%20numbers..%3C%2FP%3E%3CP%3Ethis%20is%20the%202%20cells%20again%20%2C%20cell%201%20is%20date%20form%3C%2FP%3E%3CP%3Ecell%202%20is%20also%20date%20format%3C%2FP%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3E08.09.2019%3C%2FTD%3E%3CTD%3E08.09.2019%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3Eit%20keeps%20showing%20up%20as%20just%20replica%20of%20cell%201%3C%2FP%3E%3CP%3Ehave%20done%20istext%20test%20and%20date%20in%20cell%201%20has%20form%208.9.2019%20date%20format%3C%2FP%3E%3CP%3E...%20when%20composing%20formula%20i%20dont%20knw%20what%20to%20put%20in%26nbsp%3B%20text%20format%20%2C%20line%202......%3C%2FP%3E%3CP%3Ei%20have%20at%20the%20moment%20%3DTEXT(A62%2C%22DDDD%2C%20DD.MMMM%20YYYY%22)%3C%2FP%3E%3CP%3Emy%20textbook%20says%20%3Dtext(...%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%26nbsp%3B%20is%20the%20correct%20formula...%3C%2FP%3E%3CP%3Ethanks%20for%20reply%3C%2FP%3E%3CP%3Ec...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-796983%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-796983%22%20slang%3D%22en-US%22%3Eps%20im%20using%20i%20assume%20usa%20version%20of%20excel%20on%20a%20german%20keyboard%20set%20to%20german%20input%3CBR%20%2F%3Eall%20data%20is%20going%20in%20as%20expected%20though%20%2C%20and%20using%20day%20moth%20year%20format%3CBR%20%2F%3Eall%20in%20english..%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-797063%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-797063%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384239%22%20target%3D%22_blank%22%3E%40calgar1700%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAssuming%20your%20first%20cell%20is%20A1%20you%20may%20is%20here%20text%20or%20not%20entering%20%3DISTEXT(A1)%20in%20any%20empty%20cell.%20If%20TRUE%20you%20have%20the%20text%2C%20not%20date.%3C%2FP%3E%0A%3CP%3ETo%20check%20your%20default%20date%20format%2C%20use%20Win%2BR%20(assuming%20your%20are%20on%20Windows)%2C%20here%20type%20%3CSTRONG%3Econtrol%20international%3C%2FSTRONG%3Ewhen%20Ok%20and%20see%20what%20is%20your%20Short%20date%20format.%20If%20it's%20not%20what%20you'd%20like%20to%20have%20on%20your%20PC%2C%20you%20may%20change%20it%20here%20from%20available%20options%20in%20drop-down%20list.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798635%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798635%22%20slang%3D%22en-US%22%3E%3CP%3Ei%20have%20date%20format%20in%20cell%201%26nbsp%3B%20and%20cell%202%3C%2FP%3E%3CP%3Eit%20still%20wont%20work.....%3C%2FP%3E%3CP%3Ewhat%20format%20is%20there%20supposed%20to%20be%20in%20each%20%3F%3C%2FP%3E%3CP%3Eeven%20if%20i%20change%20date%20form%20in%20cell%201%20to%20a%20different%20construct%20%2C%20eg%2001.01.19%26nbsp%3B%20it%20wont%20work%20!%26nbsp%3B%20i%20mean%20the%20form%20in%20cell%201%20doesnt%20even%20change.%3C%2FP%3E%3CP%3Enow%20on%20day%203%20of%20this%20!%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798661%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798661%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384239%22%20target%3D%22_blank%22%3E%40calgar1700%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3ECould%20you%20please%20attach%20sample%20file%20with%20values%20in%20these%20two%20cells%2C%20it'll%20be%20much%20easier%20to%20discuss.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798716%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798716%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384239%22%20target%3D%22_blank%22%3E%40calgar1700%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20this%20out%3A%3C%2FP%3E%3CP%3E%3DDATEVALUE(SUBSTITUTE(A1%2C%22.%22%2C%22%2F%22))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3CBR%20%2F%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798717%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798717%22%20slang%3D%22en-US%22%3E%3CP%3EHello%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384239%22%20target%3D%22_blank%22%3E%40calgar1700%3C%2FA%3E%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAssuming%20your%20date%20is%20in%20the%20format%20dd.mm.yyyy%20and%20is%20located%20in%20cell%20A1%2C%20you%20may%20use%20this%20formula%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DDATEVALUE(MID(A1%2C4%2C2)%26amp%3B%22%2F%22%26amp%3BLEFT(A1%2C2)%26amp%3B%22%2F%22%26amp%3BRIGHT(A1%2C2))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThen%20use%20the%20date%20format%20%2214%20March%202012%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20helps!%3CBR%20%2F%3EPReagan%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798958%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798958%22%20slang%3D%22en-US%22%3Ehey%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F384239%22%20target%3D%22_blank%22%3E%40calgar1700%3C%2FA%3Eit's%20not%20working%20because%20text%20formula%20always%20extract%20%22text%22%20%2C%20so%20i%20suggest%20you%20to%20appy%20%3DInt(b62)%20and%20then%20use%20date%20format%3CBR%20%2F%3Ehope%20this%20should%20work%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798968%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798968%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F390417%22%20target%3D%22_blank%22%3E%40Navedkhan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EINT()%20from%20text%20returns%20%23VALUE!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798976%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798976%22%20slang%3D%22en-US%22%3Ehey%20thanks%20for%20correcting%20%2C%3CBR%20%2F%3E1.%20replace%20(.)dot%20with%20%2F%20or%20-%3CBR%20%2F%3Eand%20use%20this%20formula%20%3Dtext(a62%2C%22dddd%22)%3CBR%20%2F%3Ehope%20this%20will%20work%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-798978%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-798978%22%20slang%3D%22en-US%22%3Efurther%20more%20about%20your%20query%20of%20long%20date%3CBR%20%2F%3Esuggest%20you%20to%20use%20%2F%20or%20-%20intead%20of%20(.)%20dot%20then%20use%20this%3CBR%20%2F%3E%3Dtext(a62%2C%22dddd%2Cdd%2Fmmmm%2Fyyyy)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-801423%22%20slang%3D%22en-US%22%3ERe%3A%20date%20conversion%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-801423%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F390417%22%20target%3D%22_blank%22%3E%40Navedkhan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%20you%20assume%20there%20is%20the%20date%20in%20A62.%20I%20guess%20it%20is%20a%20text%20representation%20of%20the%20date%20here.%20TEXT()%20on%20text%20returns%20exactly%20the%20same%20text.%3C%2FP%3E%3C%2FLINGO-BODY%3E
calgar1700
Occasional Contributor

trying to alter date in date format to calendar / time or text format  ie. nos. to words

this is the area  below :   the formula i used is =TEXT(A62,"dddd") in 2nd cell

 

01.10.9901.10.99  

and customised in cell format as  text ,  custom

wh y  wont it work ?  i want to display the day in words..

also,  the sheet and workbook protect function doesnt seem to work

im using office 365 trial version , 64 bit i think..

thanks

c....

13 Replies

Hello @calgar1700,

 

What exactly would you like to display in the second cell?

@calgar1700 

Are you sure you have date, not text, in first cell? You may check by ISTEXT()

@PReagan 

wish to show eg monday Ist october 1999

ie. words format not numbers..

this is the 2 cells again , cell 1 is date form

cell 2 is also date format

08.09.201908.09.2019

it keeps showing up as just replica of cell 1

have done istext test and date in cell 1 has form 8.9.2019 date format

... when composing formula i dont knw what to put in  text format , line 2......

i have at the moment =TEXT(A62,"DDDD, DD.MMMM YYYY")

my textbook says =text(...      is the correct formula...

thanks for reply

c...

 

 

 

 

ps im using i assume usa version of excel on a german keyboard set to german input
all data is going in as expected though , and using day moth year format
all in english..

@calgar1700 

Assuming your first cell is A1 you may is here text or not entering =ISTEXT(A1) in any empty cell. If TRUE you have the text, not date.

To check your default date format, use Win+R (assuming your are on Windows), here type control international when Ok and see what is your Short date format. If it's not what you'd like to have on your PC, you may change it here from available options in drop-down list.

i have date format in cell 1  and cell 2

it still wont work.....

what format is there supposed to be in each ?

even if i change date form in cell 1 to a different construct , eg 01.01.19  it wont work !  i mean the form in cell 1 doesnt even change.

now on day 3 of this ! 

@calgar1700 

Could you please attach sample file with values in these two cells, it'll be much easier to discuss.

Hello @calgar1700,

 

Assuming your date is in the format dd.mm.yyyy and is located in cell A1, you may use this formula:

 

=DATEVALUE(MID(A1,4,2)&"/"&LEFT(A1,2)&"/"&RIGHT(A1,2))

 

Then use the date format "14 March 2012".

 

Hope this helps!
PReagan

hey @calgar1700 it's not working because text formula always extract "text" , so i suggest you to appy =Int(b62) and then use date format
hope this should work

@Navedkhan 

INT() from text returns #VALUE!

hey thanks for correcting ,
1. replace (.)dot with / or -
and use this formula =text(a62,"dddd")
hope this will work
further more about your query of long date
suggest you to use / or - intead of (.) dot then use this
=text(a62,"dddd,dd/mmmm/yyyy)

@Navedkhan 

Perhaps you assume there is the date in A62. I guess it is a text representation of the date here. TEXT() on text returns exactly the same text.

Related Conversations
Extentions Synchronization
Deleted in Discussions on
3 Replies
Tabs and Dark Mode
cjc2112 in Discussions on
35 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
9 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies