I’m creating an app to manage the payment of utility bills for some families benefiting from a city hall social program. the case is as follows:
The benefit lasts for 12 months, counting from the initial date of the first payment, no matter the day, but the month. Example:
first payment: February 2023
last payment: January 2024
That said, I’ve already managed to create a mathematical column that tells me the count of months between the start date and the end date, curiously it returns 11 months, I think the problem is why I’m adding the first day of each month and not the last: February 1, 2023 to January 1, 2024.
I need a way to automate choosing the start date without including the day, just month and year. after overcoming this first obstacle, I wanted to carry out a follow-up that would make that family inactive as soon as the end date was the month following the last month of payment.
I already have the “active” value assigned to families that are receiving, and I want to assign “inactive” to those that have already exceeded the 12-month period.
Right, I did it in 2 separate columns, because when applying this formula in just one column, it adds the month to the year, returning me 2030, when the month is 7.
How would this “IF” be? I thought of an ‘if’ column to compare with the current date, if it’s greater then it would be “inactive”, if it’s less then it would be “active”, but for that, I would have to join the columns in a date format.
I’m not sure I have a full visual of what you are trying to do. Seems that you already have the start and end dates figured out, so you just need the year and month? A good calculation for that is as follows.
YEAR(date)*100+MONTH(date)
This will give you YYYYMM which is easy to use for IF comparisons because you can compare in proper numerical order.
You do not separate month and year… this is the trick to map dates and use it in logic formulas… so calculate your date syntax in the math column as Jeff and I showed you… and you go from there (multiplying year by 100 will give you a safe buffer for multiple years)
I came back, these days were very busy and I didn’t have time to check it. come on, that’s right, I already have the start date, my end date column is filled in using an excel formula. I don’t know what I’m doing wrong when trying to replicate what you suggested, but the result is an integer resulting from the sum of the month and the number of the year. and not the month and year together. my last payment date column is in mm/dd/yyyy format.
That’s not the formula I suggested. You should be using this.
Simply adding year and month together like you tried is not foolproof. 2023+8 is going to give you the same result as 2022+9. Instead, by multiplying the year by 100, you move the decimal two places to the right to give room for adding the month in those last two digits. The hierarchy of YYYY before MM will allow you to properly compare the integers in numerical and date order.
Thanks, I had tried the multiplication too, but I was multiplying the month and not the year. now that I have this data, I would like to compare it with the current month and year, how can I get this dynamic information? this way I would create an “IF” column comparing whether the last payment date is greater or less than the current month and year.
Very good, after creating this “now” column I apply the same previous formula and then compare the two columns in a new “if” column to check if it is bigger or smaller… thanks a lot for your collaboration, problem solved, I just think glide could make this process more intuitive.
