Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Aug 07, 2019 04:33 PM
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!
Solved! Go to Solution.
Aug 08, 2019 12:39 AM
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})
Aug 08, 2019 12:39 AM
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})
Aug 20, 2019 02:36 PM
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!
Aug 20, 2019 08:53 PM
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.
Aug 21, 2019 07:07 AM
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!
Aug 21, 2019 07:27 PM
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}
)
Sep 06, 2019 01:52 PM
Thanks! You’ve helped me a lot to get my system working! Wish you all the best!