If month is January set to Q1

Daniel Melton

I would like to set up a formula that is based on a dropdown of the months. Q1 would be January to March. I have been looking at using the month's formula but realized that it works off of dates, not a month dropdown. Is there a formula that I can use to update the quarter based on the month from the drop-down?

Mark Cronk

Hi Daniel,

Try, replate date@row with the name of your date column:

=IF(ISDATE(Date@row), IF(MONTH(Date@row) <= 3, "Q1", IF(MONTH(Date@row) <= 6, "Q2", IF(MONTH(Date@row) <= 9, "Q3", "Q4"))))

Work?

Mark

Daniel Melton

Hi Mark,

I have tried the formula below and am getting an unparasable error.

=IF(ISDATE(Planned@row), IF(MONTH(Planned@row <= 3, "Q1", IF(MONTH(Planned@row) <= 6, "Q2", IF(MONTH(Planned@row) <= 9, "Q3", "Q4"))))))

Mark Cronk

Try, missing a paren:

=IF(ISDATE(Planned@row), IF(MONTH(Planned@row) <= 3, "Q1", IF(MONTH(Planned@row) <= 6, "Q2", IF(MONTH(Planned@row) <= 9, "Q3", "Q4"))))

Daniel Melton

It is no longer showing unparsable, but it is no longer showing any value in the cell.

Mark Cronk

Try this variation. If it returns "no date" check the format of your Planned column. It needs to be a date column and the row needs an entry.

=IFERROR(IF(MONTH(Planned@row) <= 3, "Q1", IF(MONTH(Planned@row) <= 6, "Q2", IF(MONTH(Planned@row) <= 9, "Q3", "Q4")))), "no date")

Work?

Mark

Daniel Melton

It is giving an unparsable error again.

Mark Cronk

Hi Daniel,

This formula works for me:

=IF(ISDATE(Planned@row), IF(MONTH(Planned@row) <= 3, "Q1", IF(MONTH(Planned@row) <= 6, "Q2", IF(MONTH(Planned@row) <= 9, "Q3", "Q4"))))

Confirm that your date column is title [Planned] and that it is set as a date.

Are you in the US? If not you may need to change the commas to periods for the right syntax.

Work?

Mark

Alon Shachar

Try this - includes the FY (that you can leave out):

="Q" + IF(OR([Filter Month]@row = "April", [Filter Month]@row = "May", [Filter Month]@row = "June"), "1", IF(OR([Filter Month]@row = "July", [Filter Month]@row = "August", [Filter Month]@row = "September"), "2", IF(OR([Filter Month]@row = "October", [Filter Month]@row = "November", [Filter Month]@row = "December"), "3", IF(OR([Filter Month]@row = "January", [Filter Month]@row = "February", [Filter Month]@row = "March"), "4")))) + "FY" + IF(MONTH(TODAY()) < 4, RIGHT(YEAR(TODAY()), 2), VALUE(RIGHT(YEAR(TODAY()), 2)) + 1)