#1
|
|||
|
|||
Date range mysteriously adding 4 years
I am building out a dynamic excel analysis sheet. Although, when I paste the date for daily performance in it adds 4 years automatically. It is not a local issue ie happens on different versions of excel and different computers. The problem is from this specific document, I have tried to paste all my formulas to a new document, but that recreates the issue. Short of recreating the document, does anyone have any tips? As of now =DATE(YEAR(A2)-4,MONTH(A2),DAY(A2)) is solving my problem, but that is def not a permanent solution
Thanks!! |
#2
|
|||
|
|||
Where are you copying the data from that you are pasting in?
Could you attach a copy of the sheet here? |
#3
|
|||
|
|||
pasting from trade blotter
gebobs,
I am pasting from trade blotter, which I have attached. I am not sure where the corruption begins, but it adds 4 yrs. For instance 12/5/12 will be 12/5/16 Thanks a lot for your help |
#4
|
|||
|
|||
So "trade blotter" RTN-FIX.xlsx the date you are copying from there is in Column A.
Interesting. I see something completely different. When I paste the first date from PerformanceSummary, 12/5/12, into a blank sheet here, it becomes 12/4/08. So 4 years AND A DAY EARLIER. o-O |
#5
|
|||
|
|||
I understand what's wrong. The Performance Summary sheet has the option checked for 1904 date system.
Go to File:Options:Advanced. About 4 sections from the end is "When calculating this workbook" Uncheck "1904 date system". You should be fine now. You just have to update all your dates. |
#6
|
|||
|
|||
not working
it now shows the correct date, but does not allow me to format to a date. also, altered some formulas on the sheet. any ideas?
|
#7
|
|||
|
|||
actually now its working. I did what you said, then recreated the document copy/paste. thanks
|
#8
|
|||
|
|||
Quote:
|
Tags |
analysis, date, date field calculation |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Help me with simple IF stmt to test date within a range | tomseeley | Excel | 4 | 02-23-2014 02:13 PM |
Adding an amount to a min-max range | Nibbles | Excel | 4 | 06-13-2013 08:05 AM |
Date Range Prompt | MikeWooZ | Excel | 6 | 12-04-2011 02:10 PM |
Adding months to a Date | tsindos | Word | 4 | 10-04-2011 11:20 PM |
Using Field Codes to Calculate 5 years from a Date | vandy | Word | 1 | 09-23-2009 11:32 AM |