Rounding up a formula return
I am using the following formula to change a date into a billing quarter.
=MONTH([Year1/Deliverable1 Invoice Date]@row) / 3
It works well except for any date in January. That returns a value of .3 (1/3=.3). The system automatically rounds that down to 0 which does not work when trying to display a January date as Q1.
Feburary returns a value that rounds up to 1 which makes sense since Februaury is in Q1. All of the other months work as expected.
I have tried to nest a ROUND function in to my formula with no sucsess. Can anyone help me with this. I am thinking its something simple that I am overlooking.
Best Answer

=ROUNDDOWN((MONTH([Year1/Deliverable1 Invoice Date]@row)  1) / 3) + 1
Give that a try. Your quarters don't start at the beginning of the year either with your solution. I don't know if that was on purpose or not, but my solution puts jan  mar as 1, apr  jun as 2, jul  sep as 3, nov  dec as 4
Answers

BTW, I also tried using CEILING but was unable to get it nested in the formula.

=ROUNDDOWN((MONTH([Year1/Deliverable1 Invoice Date]@row)  1) / 3) + 1
Give that a try. Your quarters don't start at the beginning of the year either with your solution. I don't know if that was on purpose or not, but my solution puts jan  mar as 1, apr  jun as 2, jul  sep as 3, nov  dec as 4

That works! Thank you so much!
Help Article Resources
Categories
Check out the Formula Handbook template!