Convert a 12 month date to a single cell

[Slavio]

Hello,
I have in cell A2 Date in format 18-Nov-2020 UTC
I Need format 18.11.2020 in cell B2
I created this formula, but it probably won't be the right way:
=(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A3,"Nov","11"),"-",".")," UTC",""))

Dates change, so Jan = 1, Feb = 2, Mar = 3, Apr = 4, May = 5, Jun = 6, Jul = 7, Aug = 8, Sep = 9, Oct = 10, Nov = 11, Dec = 12
Please help my people

 

[Joe4]

Try this formula instead:

=TEXT(DATEVALUE(LEFT(A2,FIND(" ",A2)-1)),"dd.mm.yyyy")

Edit: Since it looks like your regional settings use ";" instead of "," to separate formula arguments, I think you need it to look like this:

=TEXT(DATEVALUE(LEFT(A2;FIND(" ";A2)-1));"dd.mm.yyyy")

 

[Slavio]

Try this formula instead:

=TEXT(DATEVALUE(LEFT(A2,FIND(" ",A2)-1)),"dd.mm.yyyy")

Edit: Since it looks like your regional settings use ";" instead of "," to separate formula arguments, I think you need it to look like this:

=TEXT(DATEVALUE(LEFT(A2;FIND(" ";A2)-1));"dd.mm.yyyy")

Joe4 Thank You, Those semicolons and commas don't bother me (I can edit that) but
Unfortunately, this formula shows me a VALUE error

 

[Joe4]

Unfortunately, this formula shows me a VALUE error

I was afraid that might happen.
Since you have different regional settings as me, I am guessing that you probably have different default date settings from me.
So, while the formula works for me in my settings (I tested it out before posting it), it doesn't work for someone using your particular date settings.

I will need to play around to see if I can come up with a different solution that might work for you.

Can you confirm that at least this part works for you?

=LEFT(A2,FIND(" ",A2)-1)

What does that return for you?

Also, can you confirm how the data is shown if the day is only a one digit value, like today.
Does it show like "3" or "03"?

 

[Joe4]

Can you also confirm what this returns for you?

=DATEVALUE("18-Nov-2020")

 

[Fluff]

It may be that xl is not recognising Nov as a month, depending on your settings. In which case you could use something like

+Fluff 1.xlsm
	A	B	C	D
1				Jan
2	18-Nov-2020 UTC	18/11/2020		Feb
3				Mar
4				Apr
5				May
6				Jun
7				Jul
8				Aug
9				Sep
10				Oct
11				Nov
12				Dec
Master

Cell Formulas
Range	Formula
B2	B2	=LEFT(REPLACE(A2,4,3,MATCH(MID(A2,4,3),D1:D12,0)),11)+0

 

[Slavio]

Joe4 Thank You, Those semicolons and commas don't bother me (I can edit that) but
Unfortunately, this formula shows me a VALUE error

It works great. I removed the gap, so it didn't work.

I'm still working on how to do it for another language, because it looks in EN but the difference is in these months
Oct = Okt
May = Maj

Since I have another language set for Excel (Not in EN)
Will you help again please?
Thanks!

 

[Slavio]

Bál som sa, že sa to môže stať.
Pretože máte iné regionálne nastavenia ako ja, hádam, že máte pravdepodobne iné predvolené nastavenia dátumu odo mňa.
Aj keď mi teda vzorec funguje v mojich nastaveniach (pred zverejnením som ho otestoval), nefunguje to pre niekoho, kto používa vaše konkrétne nastavenie dátumu.

Budem sa musieť pohrať, či nájdem iné riešenie, ktoré by pre vás mohlo fungovať.

Môžete potvrdiť, že aspoň táto časť funguje pre vás?
[CODE = xls] = DOĽAVA (A2; FIND (""; A2) -1) [/ KÓD]

Čo sa vám to vráti?

Môžete tiež potvrdiť, ako sa údaje zobrazujú, ak je deň iba jednociferná hodnota, ako napríklad dnes.
Zobrazuje sa to ako „3“ alebo „03“?

18-Nov-2020 UTC	18.11.2020
02-Nov-2020 UTC	02.11.2020
20-Dec-2020 UTC	20.12.2020
03-Nov-2020 UTC	03.11.2020
25-Oct-2020 UTC	#VALUE
17-Dec-2020 UTC	17.12.2020
09-Dec-2020 UTC	09.12.2020

 

[Slavio]

Can you also confirm what this returns for you?

=DATEVALUE("18-Nov-2020")

Datum is 18.11.2020

 

[Joe4]

Fluff came up with a suggestion for you.
I am afraid I cannot help you with the other language stuff.