On Monday, 9 April 2012 15:26:33 UTC+1, Robinsg wrote:
On Sunday, 8 April 2012 20:07:33 UTC+1, Robinsg wrote:
Hello,

I've just upgraded to Office 2010 from 2007.

I have a spreadsheet I use regularly which has a date column.

In Excel 2007 I could change the dates in the date column by copying a value from a cell e.g. 31 and the using Paste Special-Add on the date cells. This would add 31 days to the dates in the cells.

This doesn't work the same in Excel 2010. If I try the method above I'm left with a value of 31 in the cells.

Can anyone help with how I should be adding values to a date in Excel 2010?

Thanks

Glenn

Nope, still no luck.

I have auto calc set on.

I haven't made a mistake. I've been doing this for years in this spreadsheet.

Here's what happens:

I have a cell (D400) with as date of 28/07/2012 (DMY format).

In a blank cell (I400) I type 31.

I right click on I400 and select Copy.

I right click on D400 and click the Paste Special menu option.

From the Paste Special menu I leave Paste All but change operation to Add.

I press OK.

The value in D4000 now changes to 41149.

I hit Enter and it changes to 31.



Glenn

Aha, I have it.

From the Paste Special menu I change the Paste option from All to Values, Operation to Add and then OK. The correct date is shown.

If I press Escape now the new date is correctly shown.

If I press Enter instead of Escape I get 31 shown and a Ctrl Drop Down menu.

Somehow this differs from 2007 but that's fine as long as it works.

Glenn