Adding days to a date - changed in Excel 2010

[Robinsg]

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

 

[joeu2004]

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?

Works just fine for me. I suspect you simply made a mistake, perhaps a
misunderstanding of the new user-interface in 2010.

Unfortunately, this forum does not permit pasting images into postings.
That would be the easiest way to explain the correct usage. In the future,
post to the Excel forum in the Microsoft Answers Forums,
http://answers.microsoft.com/en-us/office/forum/excel.

In XL2010, after copying the cell with 31 and selecting the cell(s) that 31
should be added to, right-click and click on the words "Paste Special", not
any of the icons under the words "Paste Options". Then you will see the
same menu that you are familiar with. Of course, you would click on Add,
then OK.

(FYI, the "Paste Options" icons make it much easier to do other common
paste-special operations like pasting values and pasting functions.)

 

[Gord Dibben]

Try Paste Special>Add>OK>Esc


Gord

 

[Yunus]

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

Have you ensure the Calculations are set to Automatic?

Click Office Button(top Left Hand of worksheet).
Select Excel Options then Formulas and then ensure Workbook Calculations
is set to Auto

 

[Robinsg]

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

 

[Robinsg]

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

 

[Gord Dibben]

Don't hit Enter.........hit Esc


Gord

 

[joeu2004]

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.

Enter and Esc work the same way for me in both XL2003 and XL2007.

IIRC, Enter is a short-cut for paste after copy. (I never rely on it.)
Since the cell with 31 is still the active copy ("marching ants"), pressing
Enter will copy it to the selected cell(s).

Pressing Esc simply "cancels" the active copy; it turns off the "marching
ants".

 

[Gord Dibben]

OK>Enter has never worked for me in any version.

OK>Esc never fails.


Gord

 

[joeu2004]

Enter and Esc work the same way for me in both XL2003 and XL2007.

In case someone might misunderstand what I mean by "work the same way", let
me clarify....

I meant to say: Enter and Esc each behave in XL2010 as they each behave in
XL2003 and XL2007, contradicting Glenn's assertion that something changed in
XL2010.

But Enter and Esc behave differently from each other, as I went on to
describe.

IIRC, Enter is a short-cut for paste after copy. (I never
rely on it.) Since the cell with 31 is still the active
copy ("marching ants"), pressing Enter will copy it to the
selected cell(s).

This is exactly the behavior that Glenn stumbled upon in XL2010. My point
is: it behaves that way in XL2003 and XL2007 as well. Nothing changed.

(Hypothetically, there might be some option to disable that behavior of
Enter. If there is, I am not aware of it. But that might explain why Glenn
was unaware of it in past Excel versions.)

Pressing Esc simply "cancels" the active copy; it turns off the "marching
ants".

Again, this is the behavior that Glenn stumbled upon in XL2010. And again,
it behaves that way in XL2003 and XL2007 as well. This is the correct way
to cancel a copy operation.