date format issue when trying to do a vlookup

[Panfergrrl18]

Hi All,

This is not a pivot table question, but I have a pivot table where the columns in the pivot table show "SUM OF 02/03/2015" for example. What I am trying to do is create in the cell above that column label the date 02/03/2015, so it can be used as a reference in a vlookup. However, because the date is stored as text, I can extract the date from this column label but I can't get Excel to recognise it as a date so that I don't end up with an error in my vlookup formula. Is there a way around this? I have mocked up an example of what I've got, and in cells B1 and B2 you can see my poor attempt to get around it with no success. I'm happy to change column headings in the data that the pivot table would look up too, just trying to get some solution that is more automated than creating the date manually and dragging it across, especially if the date ranges change

Cheers,

MSample_06082105.xlsx

[Glenn Kennedy]

I'm not entirely clear as the content of B4 is different (no "Sum of") from C4, D4, etc. However, this will return a real date from a piece of text: Sum of 2/5/2015 in C4
Format as date

=SUBSTITUTE(C4,"Sum of ","")+0

[Panfergrrl18]

brilliant. Thank you!

[Glenn Kennedy]

Glad to have helped! If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. It'd also be appreciated if you were to click the add Reputation button at the foot of any of the posts of those who helped you reach a solution.