Help

Using Dates Field to Generate Text within a month

Topic Labels: Formulas
357 5
cancel
Showing results for 
Search instead for 
Did you mean: 
MSalas
4 - Data Explorer
4 - Data Explorer

So my challenge may be in my how I have my dates setup. Essentially what I'm trying to do is to utilize a date to create text based on the current month. I think an example could better explain this, e.g., I have a date that is 4/1/2024 and I'd like another column to display the text "Summaries" when it is within that month. So then if it's the following month, 5/1/2024, I want that column to display "Graphs" for that entire month. Is this something that's even possible? Is there a way in the dates to select an entire month? I think that may make the formula more straightforward. Any insights would be amazing. Thank you in advance. 

5 Replies 5
TheTimeSavingCo
16 - Uranus
16 - Uranus

Hm, try this:

 

SWITCH(
  MONTH({Date}),
  4, 'Summaries',
  5, 'Graphs'
)

Screenshot 2024-02-08 at 12.09.02 PM.png

 

This may work if I add additional columns with the graph dates (which is something I may need to do). I was hoping that I could create a formula that'll essentially be able to 'default' to the text 'Graphs' and then change to 'Summaries' the months that a 'Summary' and then back to graphs at the end of the month. Might be a big ask. I've attached a photo which may help you see what I'm working with. 

Ah, so sorry, I'm not understanding something.  Could you provide an example screenshot of what you'd like to see with some example data and a text field filled with what you'd want the formula field to be displaying?

Screenshot (105).png

So here is my table of data. Let's pretend it is the month of April 2024. The blacked out part are client names. The column in yellow is where I'd like to place a formula that would generate the text of either "graph", "annual" or "summary" based on the data to the right. I'm not sure If I'd need to generate all the columns for "graph" also or if it can default to graph if it's not a "quarterly summary" or "annual summary" due. Ideally, the in the "Due this Month" column the data there would remain for the entire month and not just the date to in the "quarterly" and "annual" columns. I hope this makes more sense and I'm very thankful for your expertise. 

Hmm that helps, but I'm still not too clear what needs to happen

Is how the formula outputs "Graph", "Annual" or "Summary" an interaction between today's date and the "Annual Summary" and "Quarterly Summary" fields? 

Specifically:
 - If today's month matches one of the months in the 'Quarterly Summary' fields, it should be "Summary"
 - If todays' month matches the "Annual Summary" month, it should be "Annual"
 - Otherwise, it's "Graph"

Is that right?