What formula can make table headers values capable of being referenced in equations?

[uptopjimbo]

Hello!

I have an excel table that I'm building for scheduling / planning purposes. I want to be able to utilize the headers as date values that I can reference for summing assigned hours that are in a column whose header date is greater than the last day of the previous week. The formula I'm using does calculate without errors but, it doesn't calculate correctly. It simply just sums everything in the row regardless of the date value input as a condition to back-check against. Screenshot attached.

I currently have this equation which calculates 32 hrs in the top row of the table:
=SUMPRODUCT(--(Table29[[#Headers],[2/28/22]:[12/26/22]]>$P$7),Table29[@[2/28/22]:[12/26/22]])

In this equation P7 is the manual date entry cell I'm using to see if the equation is working. Eventually once this works I will add an equation to auto-calculate that "TODAY()-WEEKDAY(TODAY())+1" but, for now I need to get it to work before I do anything.

Thanks in advance! Happy to provide any other information.

 

[RoryA]

Table headers are always text, and text is treated as greater than any number (dates are numbers too in Excel), hence your result. You could try wrapping the headers in a DATEVALUE function assuming those date strings match your regional format:

=SUMPRODUCT(--(DATEVALUE(Table29[[#Headers],[2/28/22]:[12/26/22]])>$P$7),Table29[@[2/28/22]:[12/26/22]])

 

[uptopjimbo]

That worked! Thank you so much. I swear I tried that but, I wasn't wrapping the entire table in that. Thanks again!