You don't need to be an 'investor' to invest in Singletrack: 6 days left: 95% of target - Find out more
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