Try this where i8 is the posted expiration date
=EOMONTH(MAX(TODAY(),I8),0)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Andrew" wrote in message
...
I am the treasurer of an NPO, got my members expiration dates in a date
field in an EXCEL spread sheet. Some members renew memberships already
expired, some others renew ahead of their membership's expiration date. In
the first case, we credit a year from the end of the current month. In the
latter, we credit a year from the end of their month in the future as to
not penalize them from renewing 'early'.

Is there some easy way to simply be able to do the following?

Suppose today's date is 1/12/2008.

Case A. The expiration date in the s/s cell already expired (Say,
12/31/2007). Would like it to be at end of the current month a year from
now. ie: 1/31/2009. ie: They buy a new year from today's date from end of
the month.

Case B. The expiration date in the s/s cell is in the future (say,
3/31/208), but the member renewed. Would like it to be a at end of THAT
month a year in the future. Want to change it to 3/31/2009. ie: They buy
a year from the upcoming renewal date, not a year from the current month
as they still have time on their 'old' membership.

Right now, I need to manually look at the cell, figure out if it already
expired or not, and manually update it. I am afraid I make mistakes and
not credit the member's renewal correctly.

Would certainly appreciate a reply here if anyone knows how to do this
programmatically! TIA.

--
-------------------------------------------------------------
Regards -

- Andrew