Displaying the date 01-01-21 as Jan

%3CLINGO-SUB%20id%3D%22lingo-sub-2395614%22%20slang%3D%22en-US%22%3EDisplaying%20the%20date%2001-01-21%20as%20Jan%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2395614%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFirstly%2C%20i'm%20so%20glad%20i%20found%20this%20forum%20and%20thank%20you%20for%20your%20help%20this%20far.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20cells%20I%20require%20the%20months%20of%20the%20year%2C%20Jan%2C%20Feb%2C%20March%20April%20etc.%20Without%20having%20to%20write%20it%20in%20words%2C%20is%20there%20a%20way%20i%20can%20write%2001-01%20and%20it%20appear%20as%20Jan%20and%20the%20same%20for%20the%20rest%20of%20the%20year%3F%3C%2FP%3E%3CP%3EThanks%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2395614%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2395754%22%20slang%3D%22en-US%22%3ERe%3A%20Displaying%20the%20date%2001-01-21%20as%20Jan%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2395754%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059726%22%20target%3D%22_blank%22%3E%40Hannah_B142%3C%2FA%3E%26nbsp%3B%2C%3C%2FP%3E%3CP%3E%3CBR%20%2F%3EI%20guess%20you%20are%20searching%20for%20this.%26nbsp%3B%3C%2FP%3E%3CP%3EUnlucky%20my%20excel%20is%20in%20german%2C%20but%20I%20hope%20it%20helps%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Schnittlauch_0-1622209441147.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F284603i0F55387F728C1ED1%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22Schnittlauch_0-1622209441147.png%22%20alt%3D%22Schnittlauch_0-1622209441147.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20regards%2C%3CBR%20%2F%3ESchnittlauch%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%22First%2C%20No%20system%20is%20safe.%20Second%2C%20Aim%20for%20the%20impossible.%20Third%20no%20Backup%2C%20no%20Mercy%22%20-%20Schnittlauch%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20answer%20helped%20you%3F%20Don't%20forget%20to%20leave%20a%20like.%20Also%20mark%20the%20answer%20as%20solved%20when%20your%20problem%20is%20solved.%20%3CLI-EMOJI%20id%3D%22lia_slightly-smiling-face%22%20title%3D%22%3Aslightly_smiling_face%3A%22%3E%3C%2FLI-EMOJI%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2395801%22%20slang%3D%22de-DE%22%3ESubject%3A%20Displaying%20the%20date%2001-01-21%20as%20Jan%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2395801%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1059726%22%20target%3D%22_blank%22%3E%40Hannah_B142%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWithout%20large%20settings%2C%20proposal%20with%20formula%20%3A))%3C%2FP%3E%3CH1%20id%3D%22toc-hId-1333713435%22%20id%3D%22toc-hId-1333713403%22%3E%3CA%20href%3D%22https%3A%2F%2Fwww.computerwissen.de%2Fsoftware%2Foffice%2Fexcel%2Ffunktionen-excel%2Fdatums-und-uhrzeitfunktionen%2Fmonatsnamen-per-excel-formel-aus-einer-zahl-erzeugen%2F%22%20target%3D%22_self%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ECreate%20month%20names%20from%20a%20number%20using%20an%20Excel%20formula%3C%2FA%3E%3C%2FH1%3E%3CP%3EYou%20can%20work%20particularly%20easily%20and%20quickly%20with%20the%20TEXT%20function.%20You%20leave%20it%20to%20Excel%20to%20convert%20the%20number%20into%20a%20month%20name.%20Use%20the%20following%20formula%20to%20represent%20the%20appropriate%20month%20name%20for%20a%20month%20number%20in%20cell%20B1%20as%20text%20in%20cell%20B2%3A%3C%2FP%3E%3CP%3E%3DTEXT(DATE(1%2C%20B1%3B1)%3B%22%20MMM%22)%26amp%3B%22.%22%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20if%20I%20could%20help%20you%20with%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%20%3CBR%20%2F%3E%20I%20know%2C%20I%20know%20nothing%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Please%20mark%20and%20rate%20this%20answer%20if%20it%20has%20helped%20you%2C%20this%20would%20also%20be%20beneficial%20for%20several%20other%20community%20members%20reading%20here.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi all

 

Firstly, i'm so glad i found this forum and thank you for your help this far.

 

In cells I require the months of the year, Jan, Feb, March April etc. Without having to write it in words, is there a way i can write 01-01 and it appear as Jan and the same for the rest of the year?

Thanks

5 Replies

Hi @Hannah_B142 ,


I guess you are searching for this. 

Unlucky my excel is in german, but I hope it helps you.

 

Schnittlauch_0-1622209441147.png

 

Best regards,
Schnittlauch

 

"First, No system is safe. Second, Aim for the impossible. Third no Backup, no Mercy" - Schnittlauch

 

My answer helped you? Don't forget to leave a like. Also mark the answer as solved when your problem is solved.

 

@Hannah_B142 

Ohne große einstellungen, vorschlag mit Formel :))

Monatsnamen per Excel-Formel aus einer Zahl erzeugen

Besonders einfach und schnell arbeiten Sie mit der TEXT-Funktion. Dabei überlassen Sie Excel die Umwandlung der Nummer in einen Monatsnamen. Verwenden Sie die folgende Formel, um zu einer Monatsnummer in Zelle B1 den passenden Monatsnamen als Text in Zelle B2 darzustellen:

=TEXT(DATUM(1;B1;1);"MMM")&"."

 

Ich würde mich freuen, wenn ich Ihnen damit helfen konnte.

 

Nikolino
Ich weiß, ich weiß nichts (Sokrates)

 

* Bitte markieren und bewerten Sie diese Antwort, wenn sie Ihnen geholfen hat, dies würde auch für mehre andere Community-Mitglieder von Vorteil sein, die hier lesen.

Thank you but it didn't work for me.
=EDATE(E69,1)

@Hannah_B142 

Depends on your default Windows short date format Excel could automatically convert two digits date month into full date of the current year displacing in date month. For example, my default format is

yyyy-mm-dd

When I enter digits as in the right it converts into the date as on the left

image.png

With other locale you may try 01/01 or 01.01

More about that is here

Excel works with two-digit year numbers - Office | Microsoft Docs

Change the date system, format, or two-digit year interpretation - Excel (microsoft.com)