Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

Formula to return the name of time period based on dates

Topic Labels: Formulas
282 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello everyone! Hope you can help.

I’m looking for a formula that will return the number of a quarter (e.g. Q1, Q2, etc.) based on the current date and the date when the customer started his subscription.

For example, if the customer subscribed on October, 1st, 2021, and today is 13 January, 2022, it should return Q2 so that we knew his subscription phase, or quarter.

Thank you!

1 Reply 1

Are you dealing with calendar quarters or relative quarters? Meaning do you want all quarters to start Jan 1, Apr 1, Jul 1, and Oct 1?
If you want calendar quarters, try:

DATETIME_DIFF(
DATETIME_PARSE(DATETIME_FORMAT(TODAY(), "YYQ"), "YYQ"), 
DATETIME_PARSE(DATETIME_FORMAT({Start Date}, "YYQ"), "YYQ"), 
"Q"
) + 1

If not, it sounds like you want the number of months between the start date and today, divided by 3:

IF(
{Start Date},
DATETIME_DIFF(TODAY(), {Start Date}, "months") / 3 + 1
)