# Two Dates reference in a Cell July 25, 2011 at 23:15:01
Specs: Windows XP
 in Excel Sheet i have two dates in format 01-Jan-2011 & 30-June-2011 and i want to show these two dates in a single cell i.e ( 01-jan-2011 to 30-june-2011), plz help me out, advance thnx. See More: Two Dates reference in a Cell

#1 July 26, 2011 at 06:16:30
 assuming your original data looks like this in A and B and you want the following in C``` Column A Column B Column C row 1 01-Jan-2011 30-June-2011 01-Jan-2011 to 30-June-2011``` Assuming excel recognizes A and B as strings/text and not datevalues, then in C you want to use:=A1&" to "&B1if they are actual MS Excel datevalues then it gets a little more complicated. This will get you started but does not give the EXACT format you are looking for.=DAY(E20)&"-"&MONTH(E20)&"-"&YEAR(E20)&" to "&DAY(F20)&"-"&MONTH(F20)&"-"&YEAR(F20)1) you need to add a zero in front of the day when then the day is less than 10. Can be achieved with an if statement2) you need to convert the month from a number to the word. I can think of a couple of ways to achieve this, but I was not sure if there was a built in function.I stopped there as I was not sure how important having the zero in front of the 1 was and how important it was to have the month as a word and not a numberif you fill D1 to D12 with the numbers 1 to 12 in sequence, and beside that in E1 to E12 you place the way you want to display your month, then the following equation will do all that you need. =IF(DAY(A1)<10,0,"")&DAY(A1)&"-"&VLOOKUP(MONTH(A1),\$D\$1:\$E\$12,2,1)&"-"&YEAR(A1)&" to "&IF(DAY(B1)<10,0,"")&DAY(B1)&"-"&VLOOKUP(MONTH(B1),\$D\$1:\$E\$12,2,1)&"-"&YEAR(B1)Though I'd still like to know if there is a built in function for converting the numerical value of the month to words, or pulling the month in words from a datevalue.*****Oh sweet! I just picked up this tid bit from another site. Much simpler then what I have above:=TEXT(A1,"dd-mmm-yy")&" to "&TEXT(B1,"dd-mmm-yy")

Report •
Related Solutions 