| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
How to change the year digit?
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 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to change the year digit?
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)*1 00,MONTH(A1+(B1*30)),DAY(A1+(B1*30))) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Eric" wrote: 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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to change the year digit?
Thank you very much for suggestions
=DATE(RIGHT(YEAR(A1+(B1*30)),2)+LEFT(YEAR(A2),2)*1 00,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" wrote: 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)*1 00,MONTH(A1+(B1*30)),DAY(A1+(B1*30))) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Eric" wrote: 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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to change the year digit?
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),MO NTH(A1)+B1,DAY(A1))),DAY(DATE(YEAR(A1),MONTH(A1)+B 1,DAY(A1)))) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Eric" wrote: Thank you very much for suggestions =DATE(RIGHT(YEAR(A1+(B1*30)),2)+LEFT(YEAR(A2),2)*1 00,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" wrote: 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)*1 00,MONTH(A1+(B1*30)),DAY(A1+(B1*30))) ------------------------------------- Pl. click ''''Yes'''' if this was helpful... "Eric" wrote: 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 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
How to change the year digit?
On Sat, 4 Apr 2009 18:54:01 -0700, Eric wrote:
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))),1 00), MONTH(DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1))),DAY(A1) ) --ron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatically convert Text with 2-digit year | Excel Discussion (Misc queries) | |||
| need formula 4 %change on both neg and pos movemnt year to year co | Excel Discussion (Misc queries) | |||
| Addendum to 2 digit year to 4 digit year | Excel Discussion (Misc queries) | |||
| Format 2 digit year to 4 digit | Excel Discussion (Misc queries) | |||
| 2 digit year in dates return 19xx not 20xx | Excel Discussion (Misc queries) | |||
Hybrid Mode
