Skip to main content

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!

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

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!

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.

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!

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}


)

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!

Reply


Terms & Conditions