Skip to main content
Solved

Formula for filtering values with a condition within a specific date

  • August 7, 2019
  • 6 replies
  • 73 views
V

Hello! I need some help of this community because I lack the knowledge to develop formulas.

I trying to create an automated financial system using some linked tables inside a base. I have expenses categorized in a field with the options ‘‘Credit’’ and ‘‘Debit’’, which I use to separate the payment methods. The expenses also have a ‘‘Date’’ field and a ‘‘Cost’’ field. I want to create a field using a formula that brings me the data from the ‘‘Cost’’ field if the expense is payed in ‘‘Credit’’ within a specific month (actual month) in order to separate this values so I can link them to another table and use a simple SUM formula to calculate my total expenses in Credit for the actual month.

So its something like: If method of payment = Credit, If date = actual month, ‘‘cost’’

I know I could simply use a filter view to see only ‘‘Credit’’ records, but as the data will be linked to another table for other operations, I cant.

I would be extremely grateful if you could help me in this!

Best answer by Kamille_Parks11

By “actual month”, do you mean Today’s month (i.e. always check if the expense happened during this current month)?

If so, the formula would be: IF(AND({Method of Payment}='Credit', DATETIME_FORMAT({Date}, 'MMYYYY')=DATETIME_FORMAT(TODAY(), 'MMYYYY')), {Cost})

6 replies

Kamille_Parks11
Forum|alt.badge.img+27

By “actual month”, do you mean Today’s month (i.e. always check if the expense happened during this current month)?

If so, the formula would be: IF(AND({Method of Payment}='Credit', DATETIME_FORMAT({Date}, 'MMYYYY')=DATETIME_FORMAT(TODAY(), 'MMYYYY')), {Cost})

V
V
  • Victor_Loja
  • Author
  • New Participant
  • August 20, 2019

By “actual month”, do you mean Today’s month (i.e. always check if the expense happened during this current month)?

If so, the formula would be: IF(AND({Method of Payment}='Credit', DATETIME_FORMAT({Date}, 'MMYYYY')=DATETIME_FORMAT(TODAY(), 'MMYYYY')), {Cost})


Hello Kamille!

Yes, Today’s month is what I actually meant with ‘actual month’. I’ve tried the formula you gave me and it worked perfectly.

I’m extremely grateful to you! Wish you all the best!

Justin_Barrett
Forum|alt.badge.img+21

Hello Kamille!

Yes, Today’s month is what I actually meant with ‘actual month’. I’ve tried the formula you gave me and it worked perfectly.

I’m extremely grateful to you! Wish you all the best!


Just tossing an alternate formula out there. Instead of comparing formatted dates, you could use the IS_SAME date function:

IF(AND({Method of Payment}='Credit', IS_SAME({Date}, TODAY(), "month")), {Cost})

This function can distinguish between the same month in different years, so that August 2018 won’t match against August 2019.

V
Kamille_Parks11
V
  • Victor_Loja
  • Author
  • New Participant
  • August 21, 2019

Just tossing an alternate formula out there. Instead of comparing formatted dates, you could use the IS_SAME date function:

IF(AND({Method of Payment}='Credit', IS_SAME({Date}, TODAY(), "month")), {Cost})

This function can distinguish between the same month in different years, so that August 2018 won’t match against August 2019.


Hello Justin!!

I didn’t think about that. You saved me from having trouble in a year from now. Thank you so much!
Now I’m able to get only the credit expenses of the current month as I needed.
I’ve been trying to adapt the formula in another field so I could also get the credit expenses from the past month based on the current month. Any idea how I would be able to do this?

Any help would be amazing!

Justin_Barrett
Forum|alt.badge.img+21

Hello Justin!!

I didn’t think about that. You saved me from having trouble in a year from now. Thank you so much!
Now I’m able to get only the credit expenses of the current month as I needed.
I’ve been trying to adapt the formula in another field so I could also get the credit expenses from the past month based on the current month. Any idea how I would be able to do this?

Any help would be amazing!


Just to be clear, I was simply offering an alternative to show a different way of solving the problem. The solution from @Kamille_Parks would also work just fine next year, as it also takes the year into account, not just the month.

This will do the job:

IF(
    AND({Method of Payment}='Credit',
    IS_SAME({Date}, DATEADD(TODAY(), -1, "month"), "month")),
    {Cost}
)
V
V
  • Victor_Loja
  • Author
  • New Participant
  • September 6, 2019

Just to be clear, I was simply offering an alternative to show a different way of solving the problem. The solution from @Kamille_Parks would also work just fine next year, as it also takes the year into account, not just the month.

This will do the job:

IF(
    AND({Method of Payment}='Credit',
    IS_SAME({Date}, DATEADD(TODAY(), -1, "month"), "month")),
    {Cost}
)

Thanks! You’ve helped me a lot to get my system working! Wish you all the best!

Terms & ConditionsAccessibility statement