1

I have calculated date ranges in excel like:

for example I have a date in B2 and substract 120 days from that like that:

enter image description here

When I copy the date the formula gets copied and not the date. I tried to copy and "only past values" but then I get weird values like 37953 ect.

enter image description here

How to only take the date value which I have calculated in my sheet. btw I am using

Our Man in Bananas
  • 5,809
  • 21
  • 91
  • 148
Carol.Kar
  • 4,581
  • 36
  • 131
  • 264

2 Answers2

4

Use PasteSpecial > Values and Number Format (in Excel 2007 and following that's the "123%" icon

Julien Marrec
  • 11,605
  • 4
  • 46
  • 63
3

According ti this http://office.microsoft.com/en-001/excel-help/figure-out-dates-by-using-formulas-RZ006177103.aspx?section=3 Dates are stored in Excel as serial numbers starting with 1, which represents January 1, 1900. That's when the Excel calendar starts.

So, set data type of the cell you're pasting to "Date" and Excel will cast value to normal date.

OR use function =TEXT(cell;"dd.mm.yyyy")

Andrii Horda
  • 170
  • 1
  • 16