Forum Discussion
Looking for a way to show upcoming payments on my das
@martin_angosto the file you uploaded is a huge help forward. That is very close to what I'm looking to do. I like the display alert example and I want a similar message to display letting me know that a bill is coming up.
I was looking at the formula you used and would need to take some time to break it down. I'm not sure how to upload a sample file for what I'm working with for more context, but this was a help.
I am so glad it was helpful! I can break the formula down for you in case you want!
The logic I used is to calculate the difference between the date of a bill and today's date. In A2 I'm using TODAY() function to return the current date always. Each day you are opening the file, it will be displayed the current date in that cell for correct calculation and display messages.
Also, as you can see, the due dates are basically simple numbers. I used, 27, 29, 5 and 1. These are the days in which these bills are going to be paid. I use only days numbers because if we set those to the actual date with month and year, you will have to update them for each month or get a list of multiple months by repeating each concept, which could be tideous.
Now, let's decompose the formula.
As my logic is to calculate the difference between today's date and the bill date and in case it's less than 7 days then display the message I did the following:
=DATE(YEAR($A$2);MONTH($A$2);D2)-$A$2
This is the base formula (the difference between the two dates). With this I convert D2's day number into an actual date. The converted date will have the same month and year as the current date displayed in A2 for comparison and mathematical purposes.
As I don't want to calculate this difference only for D2 but for all rows including bills, I use BYROW() function instead, with its corresponding LAMBDA() generating the parameter "r", which will do the same functionality but for all rows in my set range of bills. I also include now an IF() function together with AND() in order to set my criteria of being the difference < 7 days and also >0, as I don't want paid bills from more than 7 days before to be displayed:
=BYROW(D2:D5;LAMBDA(r;IF(AND(DATE(YEAR($A$2);MONTH($A$2);r)-$A$2<$C$8;DATE(YEAR($A$2);MONTH($A$2);r)-$A$2>0)
What's only left is the true and false values in case these conditions are met. For the true value I use an OFFSET() function to offset the days numbers one column to the left, to catch the value of the names of the bills. I then add my own text to combine with that name in order to write "is due in" and then I again combine the difference in days to generate a complete sentence informing about the name of the bill + is due in + "calculation of the difference in number of days" + days. For the false value I simply leave it blank.
Full formulation would be then:
=BYROW(D2:D5;LAMBDA(r;IF(AND(DATE(YEAR($A$2);MONTH($A$2);r)-$A$2<$C$8;DATE(YEAR($A$2);MONTH($A$2);r)-$A$2>0);OFFSET(r;0;-1;1;1)&" is due in "&DATE(YEAR($A$2);MONTH($A$2);r)-$A$2&" days.";"")))