Closest "X" day

[Blending_Produc]

Hey guys!
I would like help to develop a formula that would give me the day “02” closest to the date of a specific field. For example: If in the {Purchase day} field the date is 02/25/2021, I would like the formula to show me 03/02/2021, if {Purchase day} is 05/14/2021, I would like the field show me 02/06/2021.

I hope I managed to explain.

Thanks in advance!

PS: I also need help creating a formula that shows me the “02” day of the next month if the day of {date of purchase} is greater than 02. I’m trying to automate credit card billing.

I’m racking my brain a lot.

[Alexey_Gusev]

i guess 02/02/2021 is mistake/
also, what if it’s 17th of any month with 30 days? i mean, 15 days forward is 2nd of next month or 15 days back is 2nd of this month

[Blending_Produc]

Hi Alexey!
Forgiveness! I was wrong, I just edited the post.
Just explaining my idea a little better: I would like that whenever I add a purchase with a credit card to my table, the formula will return me the next invoice closing.

Thank you very much for reporting the error in my question!

Edit: I think I got it!

IF(DATETIME_FORMAT({Purchase Date}, ‘DD’)>=02, DATETIME_FORMAT(DATEADD({Purchase Date}, 1, ‘month’), ‘02/MM/YYYY’), DATETIME_FORMAT(TODAY(), ‘02/MM/YYYY’))

What do you think?

The only question now would be to be able to adapt this to a rule like: If the close date is not a business day, show me the next business day. Can you help me, please?

[Alexey_Gusev · Accepted solution]

You may simplify a bit

DATETIME_FORMAT(DATEADD({Purchase Date},
(DAY({Purchase Date}!=1)
, 'month'), '02/MM/YYYY')

regarding business day, i’ll check what formulas are available and answer later.

that will target 1st day of next month (or this, if DAY{Purchase Date}) is 1 )
usually that (true=1, false=0)works without " 1* "
, but here not. So it’s for implicit conversion

DATEADD(
  DATEADD({Purchase Date},
  1*(DAY({Purchase Date})!=1),'month'),
1-DAY({Purchase Date}),'days')

And pass it to closest workday

workday(
DATEADD(
  DATEADD({Purchase Date},
  1*(DAY({Purchase Date})!=1),'month'),
1-DAY({Purchase Date}),'days')
,1)

seems like it works. Further format operations - up to you

[Blending_Produc]

Excellent, Alexey!!

Your formula works perfectly, I really appreciate your attention.

I made some small adaptations thinking about my holiday context and language (if I decide to use the ‘MMMM’ format), see what you think:

DATETIME_FORMAT(
SET_TIMEZONE(
WORKDAY(
SET_LOCALE(
DATEADD(
  DATEADD({Data de Compra},
  1*(DAY({Data de Compra})>=16),'month'),
15-DAY({Data de Compra}),'days'), 
'America/Sao_Paulo')
,1), 
'pt-br'), 
'DD/MM/YYYY')

In this adaptation I tried to make it universal for any card bill closing date, so I replaced the “!=” with “>=”, and tested it as if the credit card bill close was on the “16” day. Is correct?

If it’s not a lot of work, I’d like to ask you to translate your formula literally, I’m new to this environment and I want to learn more and more and optimize my codes, so I’d like to understand exactly what it says. I was impressed that it was not necessary to use “IF()”.

Thank you for your attention!

[Alexey_Gusev]

In formulas, and not only, expressions (TRUE/FALSE) can be used in math operations as (1/0). Also, for example, in automations, if you put 0 to checkbox field, to “uncheck” or 1 to “check”.

there is no need to use that everywhere, as somebody, using you table/script may not understand, and IF (x=y+z),"'y",“n” is more obvious.
but in some complex formulas and multiple expressions&results i prefer to use that, especially to use more convenient “SWITCH”

like “there should be lesson with 3 students and teacher. if one student absent, lesson will go. if two - lesson cancelled. of course, lesson can’t be without teacher”

instead of mess with IF(AND((“teacher arrived”), OR(“first student arrived”… and so on, you can do
IF
((“first student arrived”)+(“2nd student arrived”)+(“3rd student arrived”)) *
(“teacher arrived”) >1,“lesson cancelled”,“lesson planned”)

or, using binary logic, you can, for example, choose all possible options for some expressions, independent each from other. E.g.
SWITCH( (exp1) + 2*(exp2) + 4*(exp3)
,0, “all false”
,1,“first is true”
,2,"second is true
…
,6,“all true except first”
,7,“all three are true”
,“cant be more that seven, smth wrong”)

[Blending_Produc]

Excellent explanation, Alexey!
I’m understanding the formulas better now, my goal is to try to make my formulas as organized as possible to avoid getting lost in their own mess hahahaha

Your SWITCH idea was very valid, I hadn’t thought of using it that way yet, I’ll start using it more.

Thank you very much for your help and all your attention.