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

Where are you copying the data from that you are pasting in?

Could you attach a copy of the sheet here?

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

Attached Files

	Performance SummaryFIX.xlsx (86.9 KB, 13 views)
	RTN-FIX.xlsx (92.7 KB, 12 views)

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

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.

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?

actually now its working. I did what you said, then recreated the document copy/paste. thanks

Roger that. Bonne chance.