Excel - returning quarters from dates

  VNAM75 12:12 22 Feb 2006
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 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

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

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

OnePlus 5 review

Alice Saey's mesmerising animation for Dutch singer Mark Lotterman

iPad Pro 10.5in (2017) review

Comment booster votre iPhone ?