Can I use MAX for quarters

[Mauranda1]

Hello everyone,

I am a newbie to excel and looking for some help !

I currently am working on a project tracker with Quarters instead of actual dates i.e project one ends on Q4 2020.

I have grouped these projects into like for like groups and would like to find the project finishing the latest i.e Group 1:

Project 1 = Q4 2020

Project 2 = Q1 2021

Project3 = Q2 2021 - this one

I was going to use the MAX equation but it seems as though this only works for actual dates i.e 01/03/2021, does anyone know a workaround this?

 

[jasonb75]

Better to use proper dates, workarounds will be inefficient and unreliable at best.

Your quarter periods are text strings so will not work with any numeric functions. Workarounds for ordering text would incorrectly evaluate Q4 2020 as being the max of your example.

edit:- In my opinion a workaround is acceptance of a bad idea rather than a solution to the problem, this one appears to work but I would be reluctant to rely on it as being accurate.

=TEXT(AGGREGATE(14,6,--SUBSTITUTE(SUBSTITUTE(A1:A3,"Q","1/")," ","/"),1),"Qm yyyy")

 

[Mauranda1]

Better to use proper dates, workarounds will be inefficient and unreliable at best.

Your quarter periods are text strings so will not work with any numeric functions. Workarounds for ordering text would incorrectly evaluate Q4 2020 as being the max of your example.

edit:- In my opinion a workaround is acceptance of a bad idea rather than a solution to the problem, this one appears to work but I would be reluctant to rely on it as being accurate.

=TEXT(AGGREGATE(14,6,--SUBSTITUTE(SUBSTITUTE(A1:A3,"Q","1/")," ","/"),1),"Qm yyyy")

Great - yes the quarters format was at the request of my boss.
I reckon I'll try this for the mean time and get those dates converted into actual dates ASAP.

 

[Mauranda1]

Great - yes the quarters format was at the request of my boss.
I reckon I'll try this for the mean time and get those dates converted into actual dates ASAP.

Better to use proper dates, workarounds will be inefficient and unreliable at best.

Your quarter periods are text strings so will not work with any numeric functions. Workarounds for ordering text would incorrectly evaluate Q4 2020 as being the max of your example.

edit:- In my opinion a workaround is acceptance of a bad idea rather than a solution to the problem, this one appears to work but I would be reluctant to rely on it as being accurate.

=TEXT(AGGREGATE(14,6,--SUBSTITUTE(SUBSTITUTE(A1:A3,"Q","1/")," ","/"),1),"Qm yyyy")

Thank you - I'll give it a try and will let you know how I get on!

 

[Mauranda1]

Better to use proper dates, workarounds will be inefficient and unreliable at best.

Your quarter periods are text strings so will not work with any numeric functions. Workarounds for ordering text would incorrectly evaluate Q4 2020 as being the max of your example.

edit:- In my opinion a workaround is acceptance of a bad idea rather than a solution to the problem, this one appears to work but I would be reluctant to rely on it as being accurate.

=TEXT(AGGREGATE(14,6,--SUBSTITUTE(SUBSTITUTE(A1:A3,"Q","1/")," ","/"),1),"Qm yyyy")

Thank you, the equation works. However, now I am faced with another problem, I would only like a selected range of cells from a filtered list. However, when using the equation, it acknowledges cells A1:A100 for example and not just A1, A2, A3, A4, A65 (which is what I would like).

Do you know how this can be achieved?

 

[jasonb75]

Do you know how this can be achieved?

By talking some sense into your boss and using proper dates.

With proper dates, the formula would be as simple as

=AGGREGATE(14,7,A1:A100,1)

With the quarters that you currently have you will need a more complex and far less efficient version of the earlier 'workaround' that may not always give you the correct results.

Something like

=TEXT(AGGREGATE(14,6,SUBSTITUTE(SUBSTITUTE(A1:A100,"Q","1/")," ","/"),1)/SUBTOTAL(3,OFFSET(A1,ROW(A1:A100)-1,0,1,1)),"Qm yyyy")

Note that I have not tested this and do not advise it as a solution regardless of whether or not it appears to work. This is purely to demonstrate the unnecessary complexity of the workaround needed with quarter dates.