• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Not sure what excel function will be used [SOLVED]

V

Virtual

New Member
Hi, I am working on an excel data and there are two columns - in the first column there are dates given in d/mmm/yy format and in the next column I haven to add Month to which the date pertains. For example - 1-May-2013 and in the Month I want May'13, and 4-Apr-2013 and in the Month I want Apr'13. Is there any excel function that I use to fill in the Months depending on the date. ?


Thanks.
 
Hi Virtual,


Try this..


Code: 
=TEXT(A1,"mmm'yy")

and drag down.. where A1 = 1-May-2013 as Date


Regards,

Deb
 
Hi, Virtual!


You could also do this:

B1: =A1

and format cell B1 as "ddd'yy" (unquoted).


With Debraj Roy's method column B contains string values and with this it contains date values as column A.


Rememeber that in both cases you'll get displayed "apr'13" instead of "Apr'13", so if you require capitalization sine qua non you should use this tweak of Debraj Roy's method:

=PROPER(TEXT(A1,"mmm'yy"))


Regards!
 
Hi, Virtual!

Glad you solved it. Thanks for your feedback and welcome back whenever needed or wanted.

Regards!
 
Alright, totally forgot but there a twist in the problem.


I need to add mmm'yy according to a given set of Date range. For example : From Oct/1/2012 - Oct/25/2012 the Month is Oct'13 and it should change to Nov'13 as soon as date changes to Oct/26/2012 . Any help on this would be appreciated .


Thanks.
 
Hi, Virtual!

=IF(A1<=DATE(2012,10,25),DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)),DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)))

and apply format mmm'yy.

Regards!
 
Hi, Virtual!


As I wrote in my first post there're 2 ways of doing the job:

a) Assigning a properly formatted text string to column B cells (Debraj Roy's suggestion)

b) Assigning the same date of column A to column B, being this last properly formatted.


So it's up to you to choose the method: what you can't do is a mix of both. If you choose the 1st, result will be a string with the format you indicated in the TEXT function:, if you choose 2nd, then result will be a date (in fact a number as Excel stores dates) which will be displayed accordingly to the format set for the cell. That's to say, there's no way to do what you've just asked, with the second method.


Regards!
 
You must log in or register to reply here.
Back
Top