Formula for looking at invoice month and assigning quarter
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:
01Invoiced JAN 2021
02Invoiced FEB 2021
03Invoiced MAR 2021
04Invoiced APR 2021
05Invoiced MAY 2021
06Invoiced JUN 2021
07Invoiced JUL 2021
08Invoiced AUG 2021
09Invoiced SEP 2021
10Invoiced OCT 2021
11Invoiced NOV 2021
12Invoiced 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 = "08Invoiced 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
Best Answer

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.
Answers

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.

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!!

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))

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

Happy to help. 👍️
Help Article Resources
Categories
Check out the Formula Handbook template!