|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 "&B1
if 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 statement
2) 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 number
if 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")