Excel - returning quarters from dates

  VNAM75 12:12 22 Feb 2006

I have a list of "dates" eg.


They are formatted as text but when I change them to dd/mm/yy format I still get the same. The pivot table refuses to group them into quarters. What I need is, alongside each date a number corresponding to the quarter of the tax year eg. 01.04.06 would give me 1 etc. Any help appreciated.

  Inky 12:53 22 Feb 2006

You could highlight the column, Edit > Find and look for all "."'s. Then replace with a "/".

The cells will at least be in date format

  VoG II 13:16 22 Feb 2006

With a date in A1


will return the quarter.

  VNAM75 13:58 22 Feb 2006

Thanks. Inky thats a very clever and simple way of converting to dates. VoG, that formula is not working for me - not sure if it's a formatting problem but I have formatted the cell to date. Anyway, thanks both. I actually ended up using a vlookup table which was a a long way of doing it but it worked. Will use your methods next time round. Thanks.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Best Black Friday Deals 2017

Black Friday Deals for Designers & Artists: Adobe, Apple, Corel Painter, Microsoft Surface, Wacom &e…

Best Black Friday Apple Deals 2017

Black Friday 2017 : date, sites participants & bonnes affaires