Date to quarter

[Anchor_Appadmin]

Hi,

I have a date column, i want a VC for quarter means

Like if month of the date is

Feb, Mar, April = Q1
May, Jun,July = Q2
Aug, Sep, Oct =Q3
Nov, De, Jan = Q4

[Aurelien]

Hi @Anchor_Appadmin

Various possibilities

IFS(
   MONTH([TimeStamp])<=3,"Q1",
   MONTH([TimeStamp])<=6,"Q2",
   MONTH([TimeStamp])<=9,"Q3",
   true,"Q4"
)

Or (less efficient, according to article below):

SWITCH(MONTH([TimeStamp]),
   1,"Q1",
   2,"Q1",
   3,"Q1",
   4,"Q2",
   5,"Q2",
   6,"Q2",
   7,"Q3",
   8,"Q3",
   9,"Q3",
   "Q4"
)

See also:

help.appsheet.com

SWITCH()

Chose-one conditional evaluation

help.appsheet.com

IFS()

First-match conditional evaluation

EDIT :
@Steve in the article IFS, I think there are one extra comma in the end of the IFS and SWITCH example, Section " Weekday Name" ?

[Steve]

Aurelien:

@Steve in the article IFS, I think there are one extra comma

Fixed! Thanks for letting me know!

[reggieneo]

Aurelien:

SWITCH(MONTH([TimeStamp]),
   1,"Q1",
   2,"Q1",
   3,"Q1",
   4,"Q2",
   5,"Q2",
   6,"Q2",
   7,"Q3",
   8,"Q3",
   9,"Q3",
   "Q4"
)

Could this be a bug?
I have tried both the sample expressions but all return “Q2”.
my timestamp runs from May to October.

Thanks

[Aurelien]

Hi @reggieneo

It works on my side.
I suspect this is due to date format.

If all of your dates are on the same day, maybe the data wrote in your Sheets is, for example:
04/05/2021
04/06/2021
04/07/2021
04/08/2021
and so on, so that “04” here is counted as the month ? If so, you may want to check your locale, here is an exhaustive list to check:

Counting number of hours from two different date time Questions

Internally, AppSheet represents dates in US format: MM/DD/YYYY; you appear to be using non-US format: DD/MM/YYYY. That could be a problem. Make sure your spreadsheet column’s cell format is Date time, that your spreadsheet’s locale is set properly, and that the app’s table locale is set correctly.