Excel - returning quarters from dates

  VNAM75 12:12 22 Feb 06
Locked

I have a list of "dates" eg.

02.05.06
02.05.06
03.05.06
04.05.06
03.05.06

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 06

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 06

With a date in A1

=ROUNDDOWN(WEEKNUM(A1)/13,0)+1

will return the quarter.

  VNAM75 13:58 22 Feb 06

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.

Best phone camera 2016/2017: Galaxy S7 vs iPhone 7 vs Google Pixel vs HTC 10 Evo vs OnePlus 3T vs…

1995-2015: How technology has changed the world in 20 years

Best Christmas Agency Projects of 2016

Super Mario Run preview | Hands-on first impressions of Super Mario Run: Mario's iPhone & iPad…