Help

Re: Formula to return the name of time period based on dates

439 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Nina_Tsarykovic
4 - Data Explorer
4 - Data Explorer

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
)