Show same dates every month.

[excelkeechak]

Hi,

i had a Column which contains Months in (MMMM) format. What i want is to show the From & To dates based on the month.I had attached an example of the same.

Kindly,help me in this regard.

[excelkeechak]

Is it not possible.???

[Domski]

In C2 copied down: =("22 "&SUBSTITUTE(B2,"'"," "))*1
In D2 copied down: =DATE(YEAR(C2),MONTH(C2)+1,20)

Dom

[Dulanic]

Nice Domski. I didn't even think to coerce excel into converting it like that with the *1. I was going to provide a longer more complicated version that it didn't need to be.

=DATE(20&RIGHT($B2,2),MONTH(1& LEFT($B2,FIND("'",$B2)-1)),22)

So that helped me too and I didnt need it

[excelkeechak]

WOW.....thats amazing.......results......

Thanks both for your Formulas.

I had been struggling with this little formula from an year................

Once again.......Thanks a ton.....

[excelkeechak]

Thanks Domski...as Dulanic said.i never thought that we can build excel formulas like this also.....

Hats off.....

[SgtSunshine]

This also works:
For the 1st date
=DATEVALUE(CONCATENATE("22/",LEFT($B2,FIND("'",$B2)-1),"/",RIGHT($B2,2)))
For the 2nd date
=DATEVALUE(CONCATENATE("26/",MATCH(LEFT($B2,FIND("'",$B2)-1),$N$2:$N$13,0)+1,"/",RIGHT($B2,2)))

if N2:N13 contains a list of January, February, March etc.

Domski's solution is better I think.

[excelkeechak]

Thanks for the replies.

I had one more query in connection to this post.how can i change the above formula if i had two or more products in the worksheet.I mean is it possible to display different dates for different products based on the vlookup using a helper column.

Any other ideas would be greatly helpful to me.

TIA.

[excelkeechak]

any suggestions or ideas please.