• 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.

LEFT function not working with dates

E

Ecel Dumbo

Member
Hello dear Excel friends,


I am trying to build a forumula to get the result "Jul-12" from the given value "Jul-11"


This is what i tried and I was getting the result as "407-12". Not sure if this is a formating issue


Date

Jul-11 =LEFT(A2,3),"-","12"


I know I am wrong. Please help correct me.


Thanks

Excel Dumbo
 
Hi ,


Can you first check out what the contents of A2 are ?


All dates are basically numbers ; 40712 is actually June 18 , 2011 if it is formatted as a date. So , if you have the value 40712 ( or 6/18/2011 or 18/6/2011 ) in a cell , and the cell is formatted to display as June 2011 , then taking LEFT(A2,3) will give you exactly what you have mentioned.


To actually get what you want , if you have a number like 40712 or a date like 6/18/2011 in a cell , you need to first convert the numeric value to a text string , after which you can do text operations such as LEFT on it.


Doing something like this :


=TEXT(DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),"mmm-yy")


should display Jun-12.


Narayan
 
Hi Ecel Dumbo,


Assuming your Jul-11 is at A1.

At B1, try this:

=TEXT(A1,"mmm")&"-"&"12"


It should show Jul-12 at B1


Regards,

Kaushik
 
Hi naarayanan and Kaushik,


I tried this and it worked, it gives me the result. But, it leads to another problem . I have explained this below


For Eg Example- Result "Jul-12" is in Worksheet1 cell B1


On Worksheet2 , cell DG25 is linked to the B1 of Worksheet 1


I have a formula below


=SUMPRODUCT(($H$20:$BE$20=$DG$26)*($H$21:$BE$21<=$DG$25)*($H28:$BE28))


which pulls out the cumulative values from Jan to Jul (H$21:$BE$21<=$DG$25) When I apply your formula mentioned in the earlier post, my formula in Worksheet 2 gives me values only till June. The big formula is supposed to give me values from Jan to July and not just Jan to June.


Hope you understood the problem, as I am working ona big file, I am not able to attach it here due to size restrictions.


Kindly advise.
 
Hi, Ecel Dumbo!


Well, that's what I definitively should call just a slight modification respect the original issues :=)

It'd be helpful if you could upload a sample file as indicated in 2nd green sticky post at this forums main page.


Regards!
 
Hi ,


I had earlier posted that you should first check the contents of A2 ; have you done so ?


Is A2 a numeric value ? If so , then most probably your SUMPRODUCT formula expects B1 also to be numeric.The formulae which have been posted by Kaushik and me , convert the numeric value in A2 to a text value in B1. In such a case , your SUMPRODUCT formula will not consider B1.


Try this formula in B1 :


=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))


and format B1 as "mmm-yy" so that it displays Jun-12.


Narayan
 
Hi Narayan,


Let me try once again.


I have two data validation lists one for 2011 (Jan-11, Feb-11, Mar-12, Apr-11)

and second data validation list 2012 (Jan-12, Feb-12, Mar-12, Apr-12)


I do not intend to use both data validation lists . I prefer to use only one data validation list, that is, 2011 . Corresponding month for 2012 should automatically come up when i select the month feom 2011 data validation drop down.


If this still does not make it clear, I will upload my big worksheet.
 
Hi ,


I will also try again !


You have two cells A2 and B1 ; at present , both these cells have drop-downs in them , through which you can select from two lists , the one for A2 going from Jan-11 through Dec-11 , and the one for B1 going from Jan-12 through Dec-12.


You wish to do away with the drop-down in B1 , and put in a formula there , so that whenever a selection is made in A2 , a corresponding entry , one year from the selection in A2 , is shown in B1.


What I want to repeat is that the method used to get B1 from A2 will depend on what type of data is present in A2 ; is it numeric ( dates are also numeric in type ) , or is it text ?


If you wish to find out this , do the following :


Make any selection in A2 , and change the cell format to Number ; see what is displayed. If it is a number , then what you have in A2 is a number. If it is not a number , then what you have in A2 is text.


If it is text , then the following formula will give you a text value , with the year increasing by 1 :


=TEXT(DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)),"mmm-yy")


If it is a number , then the following formula will give you a number , with the year increasing by 1 :


=DATE(YEAR(A2)+1,MONTH(A2),DAY(A2))


In the latter case , you will have to format the cell B1 with a custom date format "mmm-yy".


The main thing is to retain the original cell format , and get the original data type , when you switch from getting B1 using a drop-down , to getting B1 using a formula.


Narayan
 
Hi Narayana,


THank you for your elaborate and simple explaination. The format in A2 is number and hence used =DATE(YEAR(A2)+1,MONTH(A2),DAY(A2)) and then changed the format on the destination cell to "mmm-yy".


As mentioned earlier, this is still pulling out figures from Jan to June 2012 and not from Jan to July 2012


Not sure what the problem here is. If could inbox me ur email, i could send you my spreadsheet.


Thanks a lot for your time
 
Hi Ecel Dumbo,


I would also like to see what is the problem associated with!!


Can u plz also send the file here: pharmacyjukaushik@yahoo.com


Regards,

Kaushik
 
HI Narayan,


I had a further look at the problem and managed to find out what went wrong. THe formula u mentioned worked well. I had to align the date format on the data validation list and the date format on the look up range which solved the problem. Jan-12 in the validation list was coming up as - 1/12/2012, Feb-12 as 2/12/2012 and in the look up range, Jan 12- was coming up a 1/1/2012 and Feb-12 as 1/2/2012. If i use the former format , it will give me values from Jan-Jul , and if i use the later format it gives me values from Jan- Jun.


Thanks a lot for your help. This site is wonderful
 
You must log in or register to reply here.
Back
Top