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
Attached, please find the excel sheet for quick reference.
Please Login or Register to view this content.
Best Regards/VKS
Last edited by VKS; 01-14-2013 at 08:59 AM.
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
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
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>
DILIPandey, Excel rMVP
+919810929744 (India), +971528225509 (Dubai), dilipandey@gmail.com
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?
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Thanks Fotis, Dilipandey & benishiryo
@ benishiryo it gives 12-Jan-13
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>
Assuming that "12" in "Jan-12" is the year and not the day in Jan.
Formula:
Please Login or Register to view this content.
Similarly with your other formulae, except DATEVALUE(), use A1*1
For DATEVALUE() use
Formula:Please Login or Register to view this content.
Last edited by Marcol; 01-14-2013 at 09:05 AM.
If you need any more information, please feel free to ask.
However,If this takes care of your needs, please select Thread Tools from menu above and set this topic to SOLVED. It helps everybody! ....
Also
اس کی مدد کرتا ہے اگر
شکریہ کہنے کے لئے سٹار کلک کریں
If you are satisfied by any members response to your problem please consider using the small Star icon bottom left of their post to show your appreciation.
I have already explained about this in my previous post#6 and if you subtract 1 from the year, you are good to go@ benishiryo it gives 12-Jan-13
Regards,
DILIPandey
<click on below * if this helps>
Thanks a lot Experts taking some time out on this.
Best Regards/VKS
Cheers
DATE & TIME calculations are most tricky part in any application software / language /databases
Regards,
DILIPandey
<click on below * if this helps>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks