I’ve got a pivot table pulling in data based on date. So the headers are “Sum of 1/31/2024” “Sum of 2/29/2024” etc. Is there a formula I can put in another row that just pulls the date from those cells? I tried the “Right” formula, but I would need to adjust the last three formulas to pull in 10 characters instead of 9. Which is a possible solution but I was hoping to have one uniform formula to reduce the risk someone messes it up in the future.
TEXTAFTER([cell],"of ")
(as one of various solutions, depending on what you can trust about the source string)
I was going to suggest SUBSTITUTE([cell], "Sum of ", “”). I’m not an Excel expert by any means but it seems to work.
=RIGHT(LEN(cell)-7)
I always use MID. In VB and VBA the MID function has the arguments (Reference, Start, [Length]) with Length being optional - if you omit it you’ll get all the remaining characters.
Excel is similar but the Length is not optional. So just do:
=MID(cell, 8, 99)
I think you want to replace your pivot table with a SUMIFS formula on another worksheet.
I would use this. Remove the "sum of "
Or this
And then, if you want the dates to be understood as dates, rather than as text strings, you could use the DATE function, iirc.
Also, depending on which version of Excel you use, you might have trouble copying a formula that references a cell that’s part of a pivot table. If that’s the case, you can manually type the cell (e.g. “G7”) instead of picking it with the cursor. If you do that, you can copy it across a row or down a column.
Thank you everyone, these have all been very helpful. I kept running into issues with Excel not recognizing the result as a date, which messed up other formulas. So in the interest of simplicity, and because the others at my work who might need to use this workbook are not very good with formulas, I’ve just keyed the dates above the header row.
My inner Excel nerd was quite gratified playing around with all of the suggestions though!
It sounds like you’ve hardcoded the dates? Note the DATEVALUE() function in Excel, which will take text formatted dates (like what you have) and return actual Excel date/numerical values. But you know your use-case best!
Yes. I showed a coworker what I thought to be one of the simpler solutions to make sure they were comfortable with it, and their eyes bugged and it was a definite “no.” But some of these suggestions will come in handy with a different workbook later that is shared with a different coworker that is more open to learning new things.