Finding the closest quarter to my date field

[wonka1234]

Hi,

I am using the following formula to get the closets quarter date. to my date field. MY quarters would be Jan, Apr, Jul, Oct

IE I have the value  2023-07-11, I would expect to return 2023-07-31.

Using the formula :

Quarter end  = DateTimeAdd(DateTimeAdd(If DateTimeMonth([Date Approved 1]) in (1,2,3) then ToString(DateTimeYear([Date Approved 1]))+'-01-01' elseif
DateTimeMonth([Date Approved 1]) in (4,5,6) then ToString(DateTimeYear([Date Approved 1]))+'-04-01' elseif
DateTimeMonth([Date Approved 1]) in (7,8,9) then ToString(DateTimeYear([Date Approved 1]))+'-07-01'
else ToString(DateTimeYear([Date Approved 1]))+'-10-01' endif, 3, 'month'), -1, 'Day')

it is returning 2023-09-30 sadly.

How can I fix thi?

[Christina_H]

Your formula is treating the quarters as Mar/Jun/Sep/Dec.  You're taking the first date of the quarter ( e.g. 1,2,3 = January), adding three months to get the next quarter, then subtracting one day.  If you want the quarter dates to be Jan/Apr/Jul/Oct, which months should map to each one?

[binuacs]

@wonka1234 One way of doing this

[PhilipMannering]

Would 2023-05-01 also have a Quarter End of 2023-07-31?

[PhilipMannering]

If the like is affirming my question then you're looking to 'round up' rather than find the closest quarter?

Here's my attempt...