Converting Lotus formula to Excel, ugh 2

[DebbieDavis]

Hello,

I have the unenviable task of converting a VERY OLD Lotus spreadsheet to Excel, neither of which I am very good at. I am stuck on this Lotus formula:

+P20+@CHOOSE(@MONTH(P20)-1,31,@IF(@MOD(@YEAR(P20),4)=0#AND#@YEAR(P20)<>100,29,28),31,30,31,30,31,31,30,31,30,31)

Where Cell P20 = @DATE(96,2,20)

The value that goes into P20 is from a date prompt which asks the user to enter the next payment due date, which of course would be, for example, @date(03,4,20) or April 20, 2003. I really don't know where to start with converting this one. I've converted everything except this and one VLOOKUP thing, so all in all, I've actually done pretty well. Thanks for any help offered.

dd

 

[RobBroekhuis]

Hmmm... Seems like

=DATE(YEAR(P20),MONTH(P20)+1,DAY(P20))

does the trick just fine. Even seems to work when the month is December

Rob

 

[xlbo]

I think that this will do it - is recognised as a formula by excel anyway - very similar to what you had, minus the @ and with AND working BEFORE the conditions rather than after
=P20+CHOOSE(MONTH(P20)-1,31,IF(AND(MOD(YEAR(P20),4)=0,YEAR(P20)<>100),29,28),31,30,31,30,31,31,30,31,30,31)

PS - this is a VBA forum - you should really ask formula questions in the MSOffice forum
Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[xlbo]

ok - Rob wins for actually understanding the formula - I do now but was too pre-occupied with making it work rather than optimising it Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[DebbieDavis]

Awesome, works great. Thanks for the help and I'll post my next one in the proper place!!

I have a VLOOKUP question next, ha!

Thanks gain.

 

[xlbo]

Vlookup is in excel as well...... Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[DebbieDavis]

You both get stars. Geoff for being so helpful and rewriting the formula and Rob for optimising the ugly thing!! Thanks again to both. I'll post the VLOOKUP question in the excel forum.