# Excel formula question **URL:** \ **Category:** Factual Questions\ **Created:** [September 4, 2024, 6:27pm UTC](https://boards.straightdope.com/t/excel-formula-question/1006770 "2024-09-04T18:27:18Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![liirogue](https://avatars.discourse-cdn.com/v4/letter/l/dbc845/32.png) [@liirogue](https://boards.straightdope.com/u/liirogue)\ **Post date:** [September 4, 2024, 6:27pm UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/1 "2024-09-04T18:27:18Z")
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. ---
**Author:** ![Pasta](https://avatars.discourse-cdn.com/v4/letter/p/ecccb3/32.png) [@Pasta](https://boards.straightdope.com/u/Pasta)\ **Post date:** [September 4, 2024, 8:09pm UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/2 "2024-09-04T20:09:29Z")
TEXTAFTER([cell],"of ") (as one of various solutions, depending on what you can trust about the source string) ---
**Author:** ![Shoeless](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/shoeless/32/7099_2.png) [@Shoeless](https://boards.straightdope.com/u/Shoeless)\ **Post date:** [September 4, 2024, 8:13pm UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/3 "2024-09-04T20:13:11Z")
I was going to suggest SUBSTITUTE([cell], "Sum of ", “”). I’m not an Excel expert by any means but it seems to work. ---
**Author:** ![Munch](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/munch/32/5281_2.png) [@Munch](https://boards.straightdope.com/u/Munch)\ **Post date:** [September 4, 2024, 9:45pm UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/4 "2024-09-04T21:45:44Z")
=RIGHT(LEN(cell)-7) ---
**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\ **Post date:** [September 4, 2024, 11:12pm UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/5 "2024-09-04T23:12:32Z")
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) ---
**Author:** ![Palooka](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/palooka/32/2902_2.png) [@Palooka](https://boards.straightdope.com/u/Palooka)\ **Post date:** [September 4, 2024, 11:42pm UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/6 "2024-09-04T23:42:44Z")
I think you want to replace your pivot table with a SUMIFS formula on another worksheet. ---
**Author:** ![puzzlegal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/puzzlegal/32/3827_2.png) [@puzzlegal](https://boards.straightdope.com/u/puzzlegal)\ **Post date:** [September 5, 2024, 12:06am UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/7 "2024-09-05T00:06:32Z")
I would use this. Remove the "sum of " > [@Munch](#): > > =RIGHT(LEN(cell)-7) Or this > [@Palooka](#): > > I think you want to replace your pivot table with a SUMIFS formula on another worksheet. And then, if you want the dates to be understood as dates, rather than as text strings, you could use the DATE function, iirc. ---
**Author:** ![puzzlegal](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/puzzlegal/32/3827_2.png) [@puzzlegal](https://boards.straightdope.com/u/puzzlegal)\ **Post date:** [September 5, 2024, 12:11am UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/8 "2024-09-05T00:11:18Z")
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. ---
**Author:** ![liirogue](https://avatars.discourse-cdn.com/v4/letter/l/dbc845/32.png) [@liirogue](https://boards.straightdope.com/u/liirogue)\ **Post date:** [September 5, 2024, 3:38pm UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/9 "2024-09-05T15:38:57Z")
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! ---
**Author:** ![Pasta](https://avatars.discourse-cdn.com/v4/letter/p/ecccb3/32.png) [@Pasta](https://boards.straightdope.com/u/Pasta)\ **Post date:** [September 5, 2024, 5:14pm UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/10 "2024-09-05T17:14:18Z")
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! ---
**Author:** ![liirogue](https://avatars.discourse-cdn.com/v4/letter/l/dbc845/32.png) [@liirogue](https://boards.straightdope.com/u/liirogue)\ **Post date:** [September 5, 2024, 6:47pm UTC](https://boards.straightdope.com/t/excel-formula-question/1006770/11 "2024-09-05T18:47:59Z")
> [@Pasta](#): > > t sounds like you’ve hardcoded the dates? 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.