Date/Time

[cmatera]

Trying to determine if the date and the time in a cell are the last
day of the month, and if that's the case I want to perform a certain
operation.

Here's what I have so far:

=IF(NOT(DATE(YEAR(A1),MONTH(A1)+1,0)),1,0)

A1 is 2/29/08

My formula returns 0, I would expect this to return a 1 as 2/29/08 IS
the last day of February...
Any ideas why this might not be working?

Thanks

Chris

 

[Teethless mama]

=--(EOMONTH(A1,0)=A1)

 

[T. Valko]

The resaon your formula doesn't work is because NOT evaluates to FALSE.

Try one of these:

This one requires the Analysis ToolPak add-in be installed (if using Excel
versions prior to Excel 2007):

=--(DAY(A1)=DAY(EOMONTH(A1,0)))

This one works in all versions of Excel (ATP not required):

=--(DAY(A1)=DAY(DATE(YEAR(A1),MONTH(A1)+1,0)))

 

[T. Valko]

Come to think of it, you don't need the DAY functions:

=--(A1=EOMONTH(A1,0))

=--(A1=DATE(YEAR(A1),MONTH(A1)+1,0))

 

[Rick Rothstein \(MVP - VB\)]

Try this...

=IF(DAY(A1+1)=1,1,0)

where 1 is returned if A1 contains a date that is the last day of its month
and 0 otherwise. I'm assuming you will want to replace the 1 and 0 with
something else (text, formula, whatever). If, however, you really want the
output to be 1 or 0, then you can use this instead...

=--(DAY(A1+1)=1)

Rick

 

[David Biddulph]

Go through your formula a stage at a time.

For any date in February 2008 in A1, =DATE(YEAR(A1),MONTH(A1)+1,0) will
return 29th Feb.
=DATE(YEAR(A1),MONTH(A1)+1,1) would return the first day of the fiollowing
month, hence 1st March.
=DATE(YEAR(A1),MONTH(A1)+1,0) is one day before that, which is 29th
February.

All that is fairly academic, because *any* non-zero number is treated as
boolean TRUE, so when you feed that into your NOT function you get FALSE.

You have then fed that FALSE in as the first argument of your IF statement,
and the result you have asked for when the condition is FALSE is zero.

That's why it's not working as you had hoped. Other contributors have
suggested various ways of asking the right question of Excel, and thus
getting the answer you were looking for.

 

[T. Valko]

Geeez, how easy was that? <g>

Nice one!

 

[Rick Rothstein \(MVP - VB\)]

Thanks, but actually the basic idea is not original with me... I once saw a
similar test implemented in VB code in a newsgroup posting and it stuck with
me. By the way, I had the same reaction you did when I first saw it too<g>.
I am not 100% sure, but I think Dave Peterson posted the original VB coded
version that I am remembering... adapting it for worksheet formula use was
simple.

Rick