A date.quarter() would be nice. I’m surprised it’s missing.
Quarters can be subjective though. For example, January is Q1 for many people but for many others (including the company I work for) July is Q1. So, for example, right now I’m in FY19Q4. Come July I’ll be in FY20Q1 and it can be a bit confusing.
I use this to calculate the Quarter:
Switch(thisRow.[Expected Close Date].DateTimeTruncate("quarter").month(),1,"Q3",4,"Q4",7,"Q1",10,"Q2")
There may be nicer ways!
And this to calculate the Financial Year:
RelativeDate(thisRow.[Expected Close Date], 6).Year()
4 Likes
Thank you Nick! I forgot that quarters could be subjective…
1 Like
Another way of calculating my quarter starting in July is:
"Q"+toText(4-Remainder(thisRow.[Expected Close Date].DateTimeTruncate("quarter").Month(),4))
This way is potentially “cleverer” but the switch method is far more readable!
Does anyone know how I can find the quarter if I define it?
Ex: If the Time/Date column says Oct 18 2023, the Quarter column would output Q4 2023.
Would this require an IF/THEN statement?
I have the same question as @Izzi_Herman . I have a date column for “Release date” formatted as MM/DD/YYYY. I would love to calculate a quarter column by transforming the release date to a Q4 2023 format. Then I can group my table by the quarter column. Anyone know how?
hi @Jake_Reed , @Izzi_Herman
this format permits to create your own taste (with or without years, spelling of the Q)
when you have a background in Excel:
Format("{1} {2} {3}",
Concatenate("Q"),
Quotient(thisRow.theDate.Month(),3).Ceiling(),
thisRow.theDate.Year())
Cheers, Christiaan
1 Like
Thanks @Christiaan_Huizer , that seems to be working! However it’s giving me this error, do you know why?
yes @Jake_Reed , this is not a date, but a text value
yous olve the issue by putting the column on text
cheers,
1 Like
@Jake_Reed Typically this type of error results when you aren’t giving the formula the type of data it needs. To fix try toText(), toNumber(), toDate(), etc to get the data type to match what the formula is expecting.
Sometimes it can still parse it - Coda is smart enough to try text that is a number as a number, but it will still alert you that technically the pipes aren’t connected properly.
1 Like
@Johg_Ananda thanks for the tip - I’ve tried inserting totext, tonumber, todate in every which way I can think of and still cant clear that error. Its probably because I’m very new to formulas and doing it wrong. If its not asking to much, can you type out exactly where you suggested I place those in my current formulas? Thanks!
@Christiaan_Huizer I just realized what you meant by putting the column on text. That worked! Thank yall so much
1 Like
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.