Formula for looking at invoice month and assigning quarter

jmhoward

Hi there,

Formulas continue to be the one area I just can't wrap my mind around. I have a sheet where I have a column called Invoice Month. The values possible for this column are:

01-Invoiced JAN 2021

02-Invoiced FEB 2021

03-Invoiced MAR 2021

04-Invoiced APR 2021

05-Invoiced MAY 2021

06-Invoiced JUN 2021

07-Invoiced JUL 2021

08-Invoiced AUG 2021

09-Invoiced SEP 2021

10-Invoiced OCT 2021

11-Invoiced NOV 2021

12-Invoiced DEC 2021

I have another column called Invoice Quarter. I want to create a formula that looks at the Invoice Month column and assigns the appropriate Invoice Quarter. So far I have:

=IF([Invoice Month]1328 = "08-Invoiced AUG 2021", "Q3 2021")

This works well for just one month, but I am stuck on how to add the other options. For example, if an Invoice Month is JUL, AUG or SEP the result returned in the Invoice Quarter column should be Q3 2021. But if the Invoice Month is JAN, FEB or MAR, the result returned in the Invoice Quarter column should be Q1 2021.

I've tried the OR function, but I keep getting incorrect argument or unparsable errors.

Many thanks for any advice!

Jackie

Paul Newcome

Try this...

="Q" + ROUNDUP(VALUE(LEFT([Invoice Month]@row, 2)) / 3) + "" + RIGHT([Invoice Month]@row, 4)

This should work for every month and every year assuming your months are always the first two digits and the year is always the last four digits.

Paul Newcome

Try this...

="Q" + ROUNDUP(VALUE(LEFT([Invoice Month]@row, 2)) / 3) + "" + RIGHT([Invoice Month]@row, 4)

This should work for every month and every year assuming your months are always the first two digits and the year is always the last four digits.

jmhoward

Wow! That's amazing @Paul Newcome! Definitely beyond my rudimentary formula writing.

If a row doesn't have an Invoice Month, then the Invoice Quarter should be blank. I've done something like this before with a true/false statement, but how would that work into the formula?

Many thanks!!

Paul Newcome

We would use an IF statement that basically says "If the [Invoice Month]@row is not blank, then output the result of the quarter_formula (if you do not include the "value if false" portion in an IF statement, it is automatically left as blank when not true).

=IF([Invoice Month]@row <> "", quarter_formula)

Which translates to...

=IF([Invoice Month]@row <> "", "Q" + ROUNDUP(VALUE(LEFT([Invoice Month]@row, 2)) / 3) + "" + RIGHT([Invoice Month]@row, 4))

jmhoward

Thank you, thank you, thank you @Paul Newcome !! I've been struggling with this one, but your formula works perfectly!

Paul Newcome

Happy to help. 👍️