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.

Hands-on: Acer Predator Triton 700 review

D&AD Awards 2017: see the best design, advertising, illustration, animation and VR of the past year

How to lose weight with an Apple Watch