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

Report •

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 "&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")

Report •
Related Solutions

Ask Question