Formula for filtering values with a condition within a specific date

[Victor_Loja]

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!

[Kamille_Parks · Accepted solution]

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})

[Victor_Loja]

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]

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.

[Victor_Loja]

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]

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.

Victor_Loja:

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?

This will do the job:

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

[Victor_Loja]

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