Are Excel table headers always converted to text?

Question

I am trying to get table headers containing dates to auto-fill properly, so that they increase by one day between columns.

Unfortunately, Excel seems to treat table headers as text regardless of the actual cell formatting, so attempting to fill a date series simply crudely increments the year.

Example with Date formatting:

Green row:

• this is not part of a table
• all data is preserved in date format
• filling the series succeeds as Excel understands that these are date values

Red row:

• this is part of a table header (this is the only difference)
• all data is apparently converted to text, even if properly entered in a date format and cells were formatted to use a date format
• filling the series fails as Excel does not understand that these are date values

Example converted back to General formatting:

• we now see why the date fill succeeded for the green row but not the red row
• despite being entered as dates and the cells having the same date format as the green row, the red row table header "dates" did not exist as dates but as text

Question:

Is it normal for Excel to convert all values to text, regardless of formatting, for table headers?

Is there any way to override this behaviour?

Update:

To answer the question regarding why I'm using dates in table headers in the first place, I have a dynamic line chart that tracks certain values by day.

As new data is entered into the table, the table extents horizontally (normal table behaviour) and the line chart also automatically plots new lines using the new data.

Here's what a section of the worksheet looks like:

This might look okay in the screenshot but, at the end of each month, the date fills an infinite number of days in each month; it keeps counting to 40 days and beyond!

As the date is stuck as text in the header, I have to manually correct it each month.

Answer 1

I am understanding your question to mean that you have X-number of columns already headed with dates in the format of dddd, mmmm d, yyyy and wish to add columns to the right of them so the table can increase in size and the new columns can be easily given the next dates, counting up daily.

First, I would mention (reiterate) that Excel Table column headers are ALWAYS text, no matter what. Excel won't even create a Table without presenting that fact to you before proceeding.

So, the true good answer here is VBA. I'm not qualified to write what you need, and you may not be permitted to have macros in the spreadsheet anyway. But the idea would be to have a macro that takes a number for how many columns to add (several ways to do that from simply placing a value in some cell for it to read to placing an end date in a dialog box it presents and have it figure out just how many to add, and more), finds and converts the last column in the Table's header to a date it can use, creates the values that should go into the added columns' headers, formats them appropriately, and finally adds the columns requested and places a text version of the dates it just formatted into the appropriate header cells.

If you can bear the thought of a manual solution, you can create a Named Range with a formula like the following:

=LET(Source,  INDIRECT(ADDRESS(1,COLUMN()-1)),
     Date,  DATEVALUE(RIGHT(Source, LEN(Source) - FIND(",",Source) - 1)),
TEXT(Date+1,"dddd, mmmm d, yyyy")
)

Then pick the cells you need new headers in. Select the last one and type an = and the name of the Named Range you created. (Perhaps that is HeaderLabel.) Press Enter. It will likely have an error, but that's fine. Copy that cell and highlight starting with it back to the left toward the Table until all the new column header cells are selected (I call them that even though they are NOT part of the Table yet.) then Paste the copied cell.

The cells paste and the Table extends. You still have the #VALUE! error. All seems like failure... But it is not. Actually several things happened when you pasted and you need to undo a level by pressing Ctrl-Z. When you do, the cells/columns drop back out of the Table and now have nice dates, formatted as needed.

Now you just need to make THOSE (not those unfortunate error headers you had at first) part of the Table. Do this by grabbing hold of the little marker at the bottom right corner of the Table and dragging it to the right to include those columns and their new headers. The headers will convert to text, as expected.

If manually expanding the Table would be awkward due to the size of it, you can select the Table Design tab on the Ribbon menu, the look in the leftmost area of it and see Table Name and Resize Table. Select Resize Table and a dialog box pops up (somewhere) in which the range the Table currently is shows and you just change the column of the right side of that range to the new rightmost column you desire.

Can't provide a completely non-manual approach because, of course, not good enough with VBA, and because Excel simply will not permit a header cell to ever contain a formula.

You might be thinking, "Hmm... what if I do what he did... go to a header cell and type =HeaderLabel, press Enter, and see the unhappy result BUT THEN press Ctrl-Z to undo a layer of what looked like one action, but Excel saw as a couple actions. Wouldn't that leave me with the header I need?" Well, no. In that case, it really is one action for Undo purposes and pressing Ctrl-Z will only return you to the unfortunate entry with the year incremented rather than the day.

To my current knowledge, that's a hard deadend.

A final thought on approaches would be to use Power Query. It can read the existing Table, then let you Transform it by changing its copy of the Table's header entries back to dates, format them, and present it self to you whereupon you'd add your columns. It could take a formula (in M I believe, but it's supposed to be not hard to do if you know Excel's formula's) for the new column header cells that just add 1 to the previous column's date. Once adjusted, you'd load it back into the spreadsheet. Trick would be that you'd add it back into the original table, which I have never tried but should work, or into its own Table which you'd copy and paste over your original Table. The key is that PQ can easily take the text headers and transform them to real dates so just adding 1 in each of that row's new column cells would work nicely. Excel will change them back to text when they become a Table in Excel again, but you accomplished your goal, so who cares, eh?

Answer 2

I use dates all the time in headers and also wish the header would format other than as a general number/text, due to index/match formulae in the same or other tables. I input text as mmm-yy in the header column (for example) and use value(Table1[[#header],[Apr-21]]) formulae elsewhere. Largely I use the end of month date for accounting, hence eomonth(value(Table1[[#header],[Apr-21]]),0). It makes every formula unnecessarily long, given a unique number can be a table header - but noone can read the table data when the header shows 44316!!!!!!!!!