how can I input a logical date in the following format "YYYY-QQ" (ie, 2021-Q2)...

Jeff Berger

how can I input a logical date in the following format "YYYY-QQ"  (ie, 2021-Q2) so that it can be sorted and also be referenced in a formula ?

Genevieve P.

Hi Jeff,

How are you wanting to use this in a formula?

A value like this would be read as text since Date-Type columns won't be able to read this format as a date. If you were manually inputting this data into your sheet, you could use a LEFT function in a formula to reference the year by pulling the four characters on the LEFT of this cell. This depends on what you're looking to do, though, which is why I asked about what type of formula you're wanting to use this value in.

Alternatively, you could have two helper columns: one that has the date in a Date type of column, and one that notes what Quarter it is. Then if you wanted it to be displayed together you can combine the YEAR from the date column and the text from the Quarter column into one Text/Number type of column.

To do this, you would use the YEAR function to pull the year, then add (+) the - and the Quarter:

=YEAR([Date Column]@row) + " - " + [Quarter Column]@row

This would allow you to use the Date column in other formulas.

Let me know if this makes sense or not! If not, it would be helpful to know a bit more about your process (how the data is being entered) and how what different formulas you want to reference this cell in.

Cheers,

Genevieve