Have you ever formatted a date as General to get the number, then formatted back to Date? Of course you have, it’s nothing to be ashamed of. I just learned a better way. Not surprisingly, I discovered this tip by accidentally hitting the wrong hotkey.
You know that Ctrl+' (apostrophe) will copy the formula from the cell above into the current cell. It doesn’t copy the value, but the formula. One side effect of that, is that formula of a date is its integer equivalent.
Press Ctrl+' below the date to see the number, then Esc to get rid of it. It doesn’t matter if you have something in the cell already because the Esc cancels whatever you were doing.
Nice shortcut!
I accidentally pressed Alt+’ when trying this and the styles menu popped up to modify the cell style of the active cell. Never knew that one…
That’s a good one. I just hope I have an occasion to use it before I forget it. It’s certainly easier to remember than Ctrl+Shift+! followed by Ctrl+Z — which I used to know, but had to google to remember.
Another way to achieve this is the shortcut Ctrl+
(theis in the top left of the keyboard and is shared with ~).Ctrl+
is the shortcut for showing formulas, and when formulas are shown all dates are presented as their integer value.It has the benefit of showing all dates as their integer values, not just a particular one.
I picked that up on a mr excel video. He recommends it as a troubleshooting tool if formulas or pivot tables involving dates are not behaving as expected - as you can quickly see if there is a date in a spreadsheet which isn't recognised as a date.
After pressing Ctrl+, press it again to return to the regular view.
Interesting, another one to add to the bag of tricks. If you’ve just entered the date so that the active cell has moved down to the cell below that has to be the quickest way.
If the active cell is on another cell I’d find it more natural to select the cell I want to look at and use JohnW’s suggestion with General format (Ctrl + Shift + ~) or else see the number in the format cells dialog (Ctrl + 1, Tab, Home).
Something else i only noticed recently that others prob know… if you want to stay on the same cell after entering a value press Ctrl + Enter, for example to see the result of entering different values in a model. (I was aware of this for entering a formula in a selection of cells but hadn’t noticed it kept the same selection.)
Ctrl+d is similar
1. if there is value in the cell above -> copies the value into the current cell
2. if there is formula in the cell above -> copies the formula into the current cell
Lori – or switch off move down on enter (under options-“advanced”) and use the arrow keys to enter and move in the direction you want, and the enter key to enter and stay put.
I thought I used F2, F9, Esc to look at the date number without changing the format but I’ve just discovered that that doesn’t actually work in Excel. It’s still stuck in my brain from Lotus 123 days!
And finally, if you want to move a date forward by 7 days in Lotus 123 it’s: F2 F9 +7 Enter.
What is the quickest way to do it in Excel?
Oh, that’s easy. It’s just
Hopefully you want your date in the dd-MMM-yy format, which nobody ever does. On my machine, it’s
because i have http://dailydoseofexcel.com/archives/2009/06/18/entering-dates-into-cells-part-ii/ which I use so much I can’t imagine not having it.
Doug – yes, the move after enter option is another way, or select the same cell twice with Ctrl to stay on the same cell after enter. i tried looking at Excel compatibility options for Lotus 123 to see if your shortcuts would which still work. The dates have to be converted first though, so as not to be interpreted as fractions and, it’s been so long, i can’t remember 123 date format.
For incrementing a cell by a week, i’d do this via the vbe immediate window: activecell=activecell+7. From the interface dick’s suggestion seems quickest or making use of the shortcut in the post. Other ways would be to enter 7 in another cell and use Paste Special-add-values or choose the Fill Series command (available by the right-click fill options) and type 7 to enter the value in the cell below.
I thought
= 7 + {up} {enter]
@snb: that works fine for the cell below. If you want the result in the same cell you could enter the formula in the cell itself and evaluate in the formula bar which surprisingly doesn’t cause a circular reference. That would save a key stroke or two on the previous method:
in fact shift+home isn’t needed either, so the result is not far off Doug’s old 123 shortcut
also {home} only works in the first column, so i think the tweak on snb’s suggestion for same cell would be:
= 7 + {down} {up} {f9} {enter}
Have often wondered (but have never taken the time to research it) why 1/1/1900 was chosen as the date that corresponds to serial number 1. ??? And, what would I do if I needed a date prior to that?