# Today's Date in Excel, formatted **URL:** \ **Category:** Factual Questions\ **Created:** [January 14, 2013, 4:18am UTC](https://boards.straightdope.com/t/todays-date-in-excel-formatted/647131 "2013-01-14T04:18:17Z")\ **Posts on this page:** 5\ **Page:** 1
**Author:** ![Trinopus](https://avatars.discourse-cdn.com/v4/letter/t/2bfe46/32.png) [@Trinopus](https://boards.straightdope.com/u/Trinopus)\ **Post date:** [January 14, 2013, 4:18am UTC](https://boards.straightdope.com/t/todays-date-in-excel-formatted/647131/1 "2013-01-14T04:18:17Z")
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! ---
**Author:** ![Dave\_Hartwick](https://avatars.discourse-cdn.com/v4/letter/d/7feea3/32.png) [@Dave\_Hartwick](https://boards.straightdope.com/u/Dave_Hartwick)\ **Post date:** [January 14, 2013, 5:45am UTC](https://boards.straightdope.com/t/todays-date-in-excel-formatted/647131/2 "2013-01-14T05:45:45Z")
> [@Trinopus](#): > > 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. ---
**Author:** ![hibernicus](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/hibernicus/32/2802_2.png) [@hibernicus](https://boards.straightdope.com/u/hibernicus)\ **Post date:** [January 14, 2013, 6:52am UTC](https://boards.straightdope.com/t/todays-date-in-excel-formatted/647131/3 "2013-01-14T06:52:46Z")
Here’s how. Format the cell with a custom format as follows: - In the \ dialog box select the tab \ - Click on “Custom” - In the box labelled “Type” enter: yyyymm.dd - Click \ Then in the cell you have just formatted, enter the date using \+\<;\> ---
**Author:** ![Dave\_Hartwick](https://avatars.discourse-cdn.com/v4/letter/d/7feea3/32.png) [@Dave\_Hartwick](https://boards.straightdope.com/u/Dave_Hartwick)\ **Post date:** [January 14, 2013, 7:25am UTC](https://boards.straightdope.com/t/todays-date-in-excel-formatted/647131/4 "2013-01-14T07:25:18Z")
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](http://www.wiseowl.co.uk/blog/s139/short-cut-key-macros-excel.htm) ---
**Author:** ![Trinopus](https://avatars.discourse-cdn.com/v4/letter/t/2bfe46/32.png) [@Trinopus](https://boards.straightdope.com/u/Trinopus)\ **Post date:** [January 15, 2013, 12:29am UTC](https://boards.straightdope.com/t/todays-date-in-excel-formatted/647131/5 "2013-01-15T00:29:57Z")
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!