MegaSack DRAW - This year's winner is user - rgwb
We will be in touch
Yet again, I call upon those with more knowledge than I...I give thanks in advance.
I have a whooroabig spreadsheet with dates in format 31/12/2021, etc.
To help with planning, I want to see volumes within quarters. Any quick method of converting dates in this format to Year, Quarter?
Happy to have this info in another column obviously.
Thanks in advance.
If your date is in A1 then:
=ROUNDUP(MONTH(A1)/3,0)
will give the quarter as a numerical result
=YEAR(A1)
will give the year as a numerical result
=YEAR(A1)&"Q"&ROUNDUP(MONTH(A1)/3,0)
will give a string result with the year and quarter (e.g. 2021Q1).
You could also do something like:
=YEAR(A1)+(0.1*ROUNDUP(MONTH(A1)/3,0))
if you wanted a single-column result that Excel would treat as a number (e.g. 2021.1)
=YEAR(A1)&”Q”&ROUNDUP(MONTH(A1)/3,0)
I really need to save that somewhere. That’s a formula I didn’t know I needed but I’ve just thought of a bunch of uses.
Thanks