QUESTION: Hi Richard,
I have a pivot table showing date ranges, e.g., "6/1/15 - 6/7/15"
I would like to display this more succinctly on a chart, specifically "6/1-6/7".
I've created a set of cells off the pivot that I am building the chart from, so I have better control of the formats, but haven't been able to figure this one out. I'm thinking that some way of using datevalue(), and/or left() might help, but I'm struggling. If you can unravel this I would really appreciate it.
Again, more succinctly: I'm trying to convert "6/1/15 - 6/7/15" to "6/1-6/7", either through formatting or a formula.
Thank you in advance.
ANSWER: assuming the dates are ALWAYS in the format you've specified above
=LEFT(A1,3) &" - " & MID(A1,10,3)
would do it for you (as a formula, and replace my A1 with the actual values)
Thanks for the rating and feedback - based on your comments (which I should have thought) about this method should work
=LEFT(A1,SEARCH(" - ",A1)-4)&" - "&LEFT(RIGHT(A1,LEN(A1)-SEARCH(" - ",A1)-2),LEN(RIGHT(A1,LEN(A1)-SEARCH(" - ",A1)-2))-3)
Your co-workers version will work, but (IF I've read it correctly) only for dates in 2015 - the version above will remove the year part of both halves of the date regardless of year
---------- FOLLOW-UP ----------
QUESTION: Wow. Thanks Aidan - I like this solution much better! You're right (obviously) that the prior formula would only work for 2015.
Interestingly I had to make some minor tweaks to get it to work. When entered as above the results were, e.g., "6/20/2 - 6/26/2"...
So I changed the -4 to -6, and the -3 to -5. Now it seems to work perfectly.
Thank you for the follow up on this!
Interesting - I was going from what I could see in AllExperts, and the numbers were correct based on my interpretation. However, as long as you have it working that's all that matters. A little trick I used for this one is to build up the formula one bit at a time - so you put a small formula in a cell, perhaps the "SEARCH" bit and you keep going - formulas have to be kept simple, and only reference one other cell at most. Once you have done it, you can have a final formula which simply adds the bits together - THEN you can replace the references to the cells that contain the answers with the formulas they contain - not sure I've explained that very clearly, but I do find it a good way of building relatively complex formulas!