You are here:

- Home
- Computing/Technology
- Business Software
- Excel
- date formats

Advertisement

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.

Andy

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)

Aidan

support@flameenterprises.co.uk

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

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!

- Add to this Answer
- Ask a Question

Rating(1-10) | Knowledgeability = 10 | Clarity of Response = 10 | Politeness = 10 |

Comment | this was a complex problem and I hadn't even asked Aidan first - so I assume he went looking for it. awesome answer and great expertise - thanks!!! |

This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

Answers by Expert:

I have provided first hand support since `95 for Microsoft Office majoring in Word and Excel - support for all versions from 2 onwards. I'm based in the UK, so please allow for time differences when asking me questions from other parts of the world!

My background is in the insurance industry and call centre areas, but have been called upon to provide many varied solutions.**Education/Credentials**

I'm educated to UK A level standard, but as I left school some 30 years ago that is rather irrelevent - university of life has provided more of a background!