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?
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.
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!