Today's Date in Excel, formatted

I promise I won’t become an Excel bore here; this’ll be my last question for a while. Promise!

Okay, ha, ha, I got fooled by =TODAY(). I didn’t realize it updates automatically. Yesterday, it was 1/12/2013, but today it is 1/13/2013. The spreadsheet recalculates it every midnight!

Okay, I Googled, and found Ctrl + ; which puts in today’s date as a static value, so it doesn’t change every midnight. Great. But…

I want it formatted my way. I prefer a kind of “Stardate,” of yyyymm.dd Using the TEXT function, that works fine – TEXT(TODAY(),“yyyymm.dd”) – but how do I put formatting into a Ctrl + ; command? Is there a good way to do this? Is there a macro or VBA command that works?

Thank you!

Probably the easiest way is to record a macro (remember to set the shortcut key) of you typing that formula (and then, if you like, copying it and pasting it as a value). You might have to enable the Developer tab on the ribbon.

Here’s how. Format the cell with a custom format as follows:

  • In the <format cells> dialog box select the tab <Number>
  • Click on “Custom”
  • In the box labelled “Type” enter: yyyymm.dd
  • Click <OK>

Then in the cell you have just formatted, enter the date using <Ctrl>+<;>

Actually couldn’t record it all in one go. If you want to be able to use the shortcut key in all workbooks, you have to save it in PERSONAL.XLS.

Sub mFormattedToday()

’ mFormattedToday Macro

’ Keyboard Shortcut: Ctrl+Shift+R

ActiveCell.FormulaR1C1 = "=TEXT(TODAY(),""yyyymm.dd"")"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

End Sub

You could use the code above. The following link shows how to assign the shortcut key.
http://www.wiseowl.co.uk/blog/s139/short-cut-key-macros-excel.htm

Oops, the hamsters ate my reply… I thought of hibernicus’ approach, but it commits me to format a whole range of cells, which I might not want all formatted that way. (This, in itself, betrays some very sloppy designing on my part. Alas, a slob’s a slob for a’that!)

I like Dave Hartwick’s approach, and will be experimenting with that directly. It’s a bit sad to have to insert a value and then immediately copy it as a value, but if Excel is going to insist on storing formulas, this seems necessary.

I love spreadsheets! I’ve loved 'em since the old Visicalc days!

Thank you both very much for your assistance and advice!