| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
change year date
I have data that has dates of 4/25/200, 4/25/20,4/25/2020
How do I change the year to show 2006 without changing the month or day |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
change year date
=DATE(2006,MONTH(A1),DAY(A1))
-- Kind regards, Niek Otten "kevin" wrote in message ... I have data that has dates of 4/25/200, 4/25/20,4/25/2020 How do I change the year to show 2006 without changing the month or day |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
change year date
I apologize for multi-posting.
As for your formula it does not work if you type 4/25/200 in one field and use your formula you get a #value! error "Niek Otten" wrote: =DATE(2006,MONTH(A1),DAY(A1)) -- Kind regards, Niek Otten "kevin" wrote in message ... I have data that has dates of 4/25/200, 4/25/20,4/25/2020 How do I change the year to show 2006 without changing the month or day |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
change year date
Correct.
Excel is not able to work with dates before 1-1-1900. So 4/25/200 is not an Excel date, 4/25/20 is, because Excel assumes 2020 then. If you need this, you'll have to manipulate the "date" as a string (TEXT) to extract month and day. -- Kind regards, Niek Otten "kevin" wrote in message ... I apologize for multi-posting. As for your formula it does not work if you type 4/25/200 in one field and use your formula you get a #value! error "Niek Otten" wrote: =DATE(2006,MONTH(A1),DAY(A1)) -- Kind regards, Niek Otten "kevin" wrote in message ... I have data that has dates of 4/25/200, 4/25/20,4/25/2020 How do I change the year to show 2006 without changing the month or day |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
change year date
you can use the find and replace function with dates. Just enter 200 in the
find and 2006 in the replace. Otherwise you will have to use text functions because 4/25/200 is not a date as far as excel is concerned. If you want a function something like this will work =IF(ISTEXT(A1),DATE(2006,LEFT(A1,FIND("/",A1)-1),MID(A1,FIND("/",A1)+1,FIND("/",A1,1+FIND("/",A1))-FIND("/",A1)-1)),DATE(2006,MONTH(A1),DAY(A1))) You need this longer version of the formula already posted because 4/25/200 is not a date as far as excel is concerned. It treats anything before 1900 and after 9999 as text. "kevin" wrote: I have data that has dates of 4/25/200, 4/25/20,4/25/2020 How do I change the year to show 2006 without changing the month or day |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to Change Date Formats | Excel Discussion (Misc queries) | |||
| Change general format to US date format | Excel Discussion (Misc queries) | |||
| Date formula: return Quarter and Fiscal Year of a date | Excel Discussion (Misc queries) | |||
| How would I change a date cell to decrease it by business days? | Excel Discussion (Misc queries) | |||
| How do I set date to change automatically on invoices created wit. | New Users to Excel | |||
Linear Mode
