Converting date into a year

[VKS]

Dear Experts,
Looks simple though but cant find a way to convert ‘Jan-12 to 2012 using any of the following
‘Jan-12 is in cell A1 and formulas are in cell B1 through B4

Please Login or Register  to view this content.

Attached, please find the excel sheet for quick reference.
Best Regards/VKS

[Fotis1991]

One way could be to use TEXT to Columns option to saparate JAN- IN A CELL AND THEN 12 in another.

Then use =20& your cell with 12 in this

[dilipandey]

Hi VKS,

Two more options are given in green cells, see attached file:-

EF_Date_Help.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[benishiryo]

hi VKS. i'm very curious of your problem. your 1st 3 solutions should work, despite the workarounds provided. the 4th solution will not because it's wrong. could you do:
=DATEVALUE(A1)
format it to a date & let me know what date does it refer to?

[VKS]

Thanks Fotis, Dilipandey & benishiryo
@ benishiryo it gives 12-Jan-13

[dilipandey]

Nice one beni..

Hi VKS, DATEVALUE is a good option but since you need to have 2012 as output, you need to subtract 1 from the year because DATEVALUE will give you 41286 which is 12-Jan-2013 and year is 2013 here

I have observed, Excel uses current year always, when it finds that year part is missing in any date
So basically if you enter two parts only out of DATEs requirement as 3 parts (Day Month Year), excel considers those two parts as "day" and "month" only and never assumes that you have entered "Year"

Regards,
DILIPandey
<click on below * if this helps>

[Marcol]

Assuming that "12" in "Jan-12" is the year and not the day in Jan.

Please Login or Register  to view this content.

Similarly with your other formulae, except DATEVALUE(), use A1*1

For DATEVALUE() use

Please Login or Register  to view this content.

[dilipandey]

@ benishiryo it gives 12-Jan-13

I have already explained about this in my previous post#6 and if you subtract 1 from the year, you are good to go

Regards,
DILIPandey
<click on below * if this helps>

[VKS]

Thanks a lot Experts taking some time out on this.
Best Regards/VKS

[dilipandey]

Cheers


DATE & TIME calculations are most tricky part in any application software / language /databases


Regards,
DILIPandey
<click on below * if this helps>