Formula Doesn't Account For February 2

[shelby55]

Hi

I am using Excel 2003.

Skip helped with a formula for finding the month that is 5 months less than the user selected "dateSelected" but it doesn't account for February. In other words, when July 31 is selected, March 3 shows instead of Feb but March is already selected for the 31 (and only one March of data exists).

The code is:

=IF(DATE(YEAR(DateSelected), MONTH(DateSelected)-5, DAY(DateSelected))<MIN(MyDates),MIN(MyDates),DATE(YEAR(DateSelected), MONTH(DateSelected)-5, DAY(DateSelected)))

Any assistance greatly appreciated, thanks.

 

[SkipVought]

Use this
[tt]
=IF(DATE(YEAR(DateSelected), MONTH(DateSelected)-4, 0)<MIN(MyDates),MIN(MyDates),DATE(YEAR(DateSelected), MONTH(DateSelected)-4, 0))
[/tt]


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

HI

Awesome, thanks very much...works great!

 

[SkipVought]

Sorry that I overlooked that detail originally.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[GlennUK]

Can you say what you want the results to be for 31 July, 30 July, 29 July, and 28 July, less 5 months? Would they all return 28 Feb? ( assuming a non-leap year )

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[GlennUK]

D'oh. all done and dusted by the time I replied .... again!

Cheers, Glenn.

Beauty is in the eye of the beerholder.

 

[shelby55]

Hi

But the fact that you responded was still appreciated, Glenn! Thanks.

 

[AnotherHiggins]

I think that this would work:

[tab][COLOR=blue white]=Max(EOMonth(DateSelected,-5), MIN(MyDates))[/color]

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[SkipVought]

Sleek 'n' slick!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}