How do I change the Date Format in Header

Question

When setting up n Excel Sheet's Header and include the current 'updated' date, how do I change it's format from the Default "YYYY-MM-DD" to "DD-Mmm-YY"?

Answer 1

Is the current date concatenated with something else in the cell or the only thing in the in the cell?  If it is the only thing, then:

Select all of the cells that you want to be formatted with this date format.  Open the Format Cells Dialogue Box by right clicking in the selection and choosing Format Cells. Select the Number tab, and Custom.  In the Type: line, enter dd-mmm-yy as below and click OK.

Answer 2

If you need the date to be concatenated with something else in the cell, use this formula:

="Today "&TEXT(TODAY(),"dd-mmm-yy")

Where "Today " is the text you want included.  Be sure to include the space inside the quotes to put a space between the text and the date in the concatenated entry.

Since this is based on the TODAY() formula it will automatically update to the current date any time the workbook is open.

Answer 3

@Hi Rich-M,

I think that the OP wants to format the date in the Header (ie. as in Headers and Footers etc).

AFAIK there is no way of doing this directly in the Header. It can be done with VBA code (macros) however, it is not dynamic and the code needs to be run each time that it requires updating. If only required for printing purposes then I believe that the code could be placed in the Before Print event so that the code runs automatically prior to printing (I have not previously tested this but I am fairly sure that it should work)

@Don_McIsaac,

If you would like the VBA code solution then please let me know if you would like it to be in the Before Print event and I will test it and see if it will work as required otherwise you would need a button to run the code prior to printing.

Answer 4

Thanks OssieMac,

Makes sense.  I didn't take time to contemplate the multiple meanings of the terms!

However, as a work-around, depending on the way the workbook is set up and what additional processes you might need that wouldn't work like this, it would be possible to put the date in Row 1 with the column headers in Row 2, adjust the top margins a little smaller and Row 1 row height a little bigger so that it looks like a header and set the Print Titles to print Row 1 on all sheets. Just a thought.