Procedure to update a DATE field to either a year from now or a year from what is in the field?

[Andrew]

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.

 

[Don Guillett]

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

 

[Don Guillett]

If you don't have the toolpak installed.
=DATE(YEAR(MAX(TODAY(),I7)),MONTH(MAX(TODAY(),I7))+1,1)-1

 

[Mike Fogleman]

Don's example formula should probably have ended with a 12 instead of 0 to
give a date 12 months from MAX date:

=EOMONTH(MAX(TODAY(),I8),12)

If you have a column where there is a date when the Renewal fee was
received, then reference that date in the formula instead of TODAY().

Without more info as to where things are on your sheet we cannot give more
specific help or code.

Mike F

 

[Don Guillett]

to add a year
=DATE(YEAR(MAX(TODAY(),I7))+1,MONTH(MAX(TODAY(),I7))+1,1)-1

 

[Don Guillett]

Thanks for the catch. I also updated non eomonth formula

 

[Andrew]

to add a year
=DATE(YEAR(MAX(TODAY(),I7))+1,MONTH(MAX(TODAY(),I7))+1,1)-1

OK for the replies folks!

So I placed the cursor on one of the cells containing a date I'd like to
change (the actual date in the edit line at top of screen showed
"10/31/2008", and overrode that date with the formula:
"=DATE(YEAR(MAX(TODAY(),I7))+1,MONTH(MAX(TODAY(),I7))+1,1)-1" as directed.
Now the date appears in the
cell as: 1/31/2009 INSTEAD of 10/31/2009. (Since the original date was
AHEAD of today's date, I want to simply ADD a year to the existing cell as
in case #B above.)

Am I doing something wrong with this formula?

I thought I could simply focus the cursor on a particular cell containing
the date and enter the formula thru pasting to have the date appear in the
cell correctly regardless of whether it is case #A or #B above.

Maybe I wasn't clear?

 

[Don Guillett]

Perhaps you should put the formula somewhere else and change the i7 in the
formula to refer to the cell you mention.