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")))
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 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?
@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.
@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...
Up until today I've restricted my understanding of datamesh to copying values from sheet to sheet. However, I just discovered today that you can use it WITHIN a sheet! What does this solve? If you ever need to copy a value from column A to column B, but do not want to use a formula, then this process is perfect. It also…
Here is a way to create reminders for Proof reviewers. This solution will: Send a reminder 5 days after the initial invite, if no one has responded to a Proof review request. Send a reminder 5 days after the last reminder, if no one has responded to a Proof review. Reset when a new Proof version is created. Some useful…
This initial solution is based on form submissions being made and each form submission triggering the creation of a set of "template" child rows per submission WITHOUT the need for a 3rd party app, the premium add-on Bridge, or the API. So if your form submissions look like this: Your working sheet could look like this:…
©2023. All Rights Reserved Smartsheet Inc.