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

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

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

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

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

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

--
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

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

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

"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

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Don Guillett" wrote in message
...
If you don't have the toolpak installed.
=DATE(YEAR(MAX(TODAY(),I7)),MONTH(MAX(TODAY(),I7)) +1,1)-1

--
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

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

Thanks for the catch. I also updated non eomonth formula

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Mike Fogleman" wrote in message
...
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

"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