Mr Excel & excelisfun Trick 57: Extract Many Years from Just Two Years

[loyaus]

Thanks for giving simple way to extract dates.

[ExcelIsFun]

You are welcome!

[rajanverma100]

I like VBA Macro,No much calculation of Formulas no more data added into Excel file.. simply just run the macro and complete My Work..

In Function My Answer is this

=IF(COLUMNS($B$1:B1)<=(RIGHT($­A1,4)-LEFT($A1,4)+1),LEFT($A1,­4)+COLUMNS($B$1:B1)-1,"")

[ExcelIsFun]

Cool!

[rajanverma100]

I like VBA Macro,No much calculation of Formulas no more data added into Excel file.. simply just run the macro and complete My Work..

[ExcelIsFun]

Yes, VBA is a great way to go!

[mmyett]

Hi Mike. I don't watch the videos until I've given the problem a try myself and my answer is so ugly, that I'd like to know if before I give up and watch should I keep trying for a more elegant answer or is it just an ugly solution? Here is my try so far: =IF(COLUMNS($C$1:C1)-1<=MID($A­3,FIND("-",$A3,1)+1,99999)-LEF­T($A3,FIND("-",$A3,1)-1),LEFT(­$A3,FIND("-",$A3,1)-1)+COLUMNS­($C$1:C1)-1,"") then copy over and down. This seems like the kind of problem VBA would be good for. Wish I knew how.

[ExcelIsFun]

I don't think it needs to be that complcated. Watch the video and see if you agree. It basically comes down to LEFT, RIGHT and COLUMNS...

[ExcelIsFun]

I am like you, though, I always want to solve it myself first. After hanging out at the Mr Excel Message Board for years though, I understand that I almost never get the best most efficient solution, so after i try, I look at other people's cool solutions!

[krn14242]

Happy Thanksgiving guys... Thanks for all the great videos.

[ExcelIsFun]

Happy Thanksgiving to you too!!

[krn14242]

Hmm, let's see. I have to give it to Mike again, Bill, you lost me at i + ctr... :)

[ExcelIsFun]

Some do love the VBA, though!

[mysimonhughes]

Like it! Very nice Mike, don't understand the VBA thing at all but the formula is great.

[ExcelIsFun]

I do not know VBA too well, but the VBA made sense to me!

I just wish I knew VBA...