How to change the year digit?

[Eric]

Does anyone have any suggestions on how to change the year digit?
In cell A1, there is a given date, and
In cell B1, there is a number of month, which will add into the date in cell
A1.
In cell C1, it should return the result.

For example,
In cell A1, 1-Jul-2098. and in cell A2, 5-Apr-2009 for today
In cell B1, 21
In cell C1, it return 1-Apr-2100 by Date(year(A1),month(A1)+B1,day(A1))

I would like to keep all date into the same century as today, if today in
cell A2 is 21 century, then 2100 year should become 2000 year.

For example,
In cell A1, 1-Jul-2098. and in cell A2, 5-Apr-2209 for today
In cell B1, 21
In cell C1, it return 1-Apr-2200

I would like to keep all date into the same century as today, if today in
cell A2 is 22 century, then 2100 year should become 2200 year.


Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

 

[Sheeloo]

In D1 try
=DATE(RIGHT(YEAR(C1),2)+LEFT(YEAR(A2),2)*100,MONTH(C1),DAY(C1))

Or use the above formula in C1 after replacing C1 in the above formula by
the formula used you in C1

eg. if you are using this in C1
=A1+(B1*30)

use this in C1 ...
=DATE(RIGHT(YEAR(A1+(B1*30)),2)+LEFT(YEAR(A2),2)*100,MONTH(A1+(B1*30)),DAY(A1+(B1*30)))

 

[Eric]

Thank you very much for suggestions

=DATE(RIGHT(YEAR(A1+(B1*30)),2)+LEFT(YEAR(A2),2)*100,MONTH(A1+(B1*30)),DAY(A1+(B1*30)))

We cannot assume 30 days for a month, if not, error will be occurred on
counting day and month.
Do you have any more suggestions?
Eric

 

[Sheeloo]

I thought you already had the formula for C1...

Anyway use this

=DATE(RIGHT(YEAR(DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))),2)+LEFT(YEAR(A2),2)*100,MONTH(DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))),DAY(DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))))

 

[Ron Rosenfeld]

Does anyone have any suggestions on how to change the year digit?
In cell A1, there is a given date, and
In cell B1, there is a number of month, which will add into the date in cell
A1.
In cell C1, it should return the result.

For example,
In cell A1, 1-Jul-2098. and in cell A2, 5-Apr-2009 for today
In cell B1, 21
In cell C1, it return 1-Apr-2100 by Date(year(A1),month(A1)+B1,day(A1))

I would like to keep all date into the same century as today, if today in
cell A2 is 21 century, then 2100 year should become 2000 year.

For example,
In cell A1, 1-Jul-2098. and in cell A2, 5-Apr-2209 for today
In cell B1, 21
In cell C1, it return 1-Apr-2200

I would like to keep all date into the same century as today, if today in
cell A2 is 22 century, then 2100 year should become 2200 year.


Does anyone have any suggestions?
Thanks in advance for any suggestions
Eric

Try this:

=DATE(TRUNC(YEAR(A2),-2)+MOD(YEAR(DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))),100),
MONTH(DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))),DAY(A1))

--ron