List of date formula I have used repeatedly

Emilio Wright

Returns either "Prior Quarter", "Current Quarter", "Next Quarter" or "Future Quarter" for a date column on your sheet. For this example my column name was [Go-Live].

=IF(OR(YEAR([Go-Live]@row) < YEAR(TODAY()), AND(YEAR([Go-Live]@row) = YEAR(TODAY()), ROUNDUP(MONTH([Go-Live]@row) / 3, 0) < ROUNDUP(MONTH(TODAY()) / 3, 0))), "Prior Quarter", IF(AND(YEAR([Go-Live]@row) = YEAR(TODAY()), ROUNDUP(MONTH([Go-Live]@row) / 3, 0) = ROUNDUP(MONTH(TODAY()) / 3, 0)), "Current Quarter", IF(AND(YEAR([Go-Live]@row) = YEAR(TODAY()), ROUNDUP(MONTH([Go-Live]@row) / 3, 0) = (ROUNDUP(MONTH(TODAY()) / 3, 0)) + 1), "Next Quarter", "Future Quarter")))

Dale Murphy

Has anyone documented the syntax for formatting specialized text (such as a dollar amount) when that text is in a formula?

(I use one method of concatenation alot, namely =[column name] + " some text " + [numeric column name] where the number from the same column needs to be a specific format.

Dale

Paul Newcome

@Dale Murphy I'm not sure your question is related to the original thread. Are you able to provide more detail as to what exactly you are wanting to accomplish?

Dale Murphy

@Paul Newcome The relationship was tenuous I admit. I was thinking of the need to take an existing column, manipulate it, and present it in another column in a new form.

For example, a formula may calculate the total cost of a task based on two or three columns of input. In the total cost column the formula would be a simple addition, and the column would be formatted to show the currency symbol and the desired number of decimal places.

If though, someone wanted to put that result in a header row, in a different column, and combine it with text, you might use ="Total task cost is " + [total cost]10 as the formula.

What I am looking for is a guide to how to get that number to display in the desired format when it is part of the formula. So the result we want is Total task cost is $4,002.90. I don't seem to be able to get the dollar sign, comma or correct number of decimals to show up. (Other formats of interest are percentages ...)

Hope that is clearer, and sorry for hijacking the earlier thread.

dm

Paul Newcome

@Dale Murphy Right now that is a very manual and tricky process and varies greatly depending on what exactly is being pulled in. I have two suggestions for you...

1. Check out the new product enhancement request method in the Topics. If someone has already suggested it, vote it up. If no one has, feel free to submit a new idea.
2. Start a new thread. I would suggest a discussion as opposed to a question. You can tag me in that, and we can start compiling a list of different ways to accomplish this for different variables. The $ is the easy part. If the decimal is not pulling through, then that is relatively straightforward as well. Percentages are pretty straightforward too. It is the commas that start to get tricky.