Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding days to a date - changed in Excel 2010
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding days to a date - changed in Excel 2010
"Robinsg" wrote:
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.) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding days to a date - changed in Excel 2010
Try Paste SpecialAddOKEsc
Gord On Sun, 8 Apr 2012 12:07:33 -0700 (PDT), 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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding days to a date - changed in Excel 2010
In article <10338193.3650.1333912053427.JavaMail.geo-discussion-
forums@vbab2, says... 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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding days to a date - changed in Excel 2010
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding days to a date - changed in Excel 2010
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding days to a date - changed in Excel 2010
Don't hit Enter.........hit Esc
Gord On Mon, 9 Apr 2012 07:26:33 -0700 (PDT), Robinsg wrote: I hit Enter and it changes to 31. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding days to a date - changed in Excel 2010
"Robinsg" wrote:
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". |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding days to a date - changed in Excel 2010
OKEnter has never worked for me in any version.
OKEsc never fails. Gord On Mon, 9 Apr 2012 09:08:21 -0700, "joeu2004" wrote: "Robinsg" wrote: 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". |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Adding days to a date - changed in Excel 2010
Clarification.... I wrote:
"Robinsg" wrote: 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. 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. I wrote: 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.) I wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to subtract 60 days from a date (05/25/2010) 2003excel | Excel Worksheet Functions | |||
Adding days to a date cell to get a new date | Excel Worksheet Functions | |||
Adding 28 days to a date | Excel Discussion (Misc queries) | |||
Adding # of days to a date | Excel Worksheet Functions |