3

I use Google Sheets to track our household financial budget. I've been slowly refining the functionality over the years, but have arrived at a point where I can't figure out how to achieve the next goal.

Across the top of the sheet (row 1), starting in column D, I have dates spaced out every two weeks (the schedule on which I get paid). Down the columns I have listed values for whatever needs to get paid out of that paycheck (rent, electric bill, student loan payment, etc.). What I want to do is, for each row in column B, put a value (1-31) for the day of the month that the bill is due on. If that value falls between the day I get paid and the day before the next time I get paid (e.g. DAY(D$1) and DAY(E$1)-1), I want to list the value in column C.

What I've come up with so far is this: =if(AND(DAY(D$1)>$B22,DAY(E$1)-1<=$B22),$C22,"")

It doesn't work. How can I make it do what I want?

  • Do these inequalities go in the right direction? They say that $B22 should be before the first date (D) but after the second (E). – user135384 Dec 20 '16 at 2:52
  • I think I've been staring at this problem too long and may be missing the obvious. Can you provide an example? – J.D. Ray Dec 20 '16 at 5:45
  • No, can you provide an example of what's in your D1 and E1? – user135384 Dec 20 '16 at 5:50
  • As an example of what I'm struggling with, consider these two pay dates in 2017: 3/24/2017 and 4/7/2017. I need to pay rent on 4/1/2017, so I plan it to come out of the paycheck I get on 3/24/2017. If I have a 1 in column B, how do I get the cell in column I (where 3/24 lands) to decide that it's time to pay rent? – J.D. Ray Dec 20 '16 at 5:52
  • 1
    Having just 1 in column B is a problem because the month is not specified. Comparing day numbers doesn't work if month is not taken into account. One way to fix this is to have a cell for month and create a date with "date" formula, then compare the date top the content of row 1. – user135384 Dec 20 '16 at 6:28
2

When you enter days in column B, there should be also some indication of what month these refer to. Without this information, meaningful date comparisons are impossible.

Let's suppose you put the relevant month, like "12" for December, in B1. Then the formula

=date(year(today()), $B$1, $B22)

returns the date with the month as in B1, day as in B22, and the current year. But considering that one should also prepare for bills that are due next year, it may be better to have a "year" cell too: say, B2 has the year in which these bills are due.

=date($B$2, $B$1, $B22)

You can use this expression in formulas for comparisons with other dates:

=if(AND(D$1 > date($B$2, $B$1, $B22), E$1-1 <= date($B$2, $B$1, $B22)), $C22, "")

This is more effective and reliable than trying to compare DAY(), MONTH() and YEAR() parts in nested If statements.

| improve this answer | |
  • 1
    But I'm only interested in having the day of the month that the bill is due on in column B. That way, as the calculation is carried forward, it can figure out if a bill is due during a cycle no matter what the month or year is. – J.D. Ray Dec 20 '16 at 22:59
1

Figured it out: =IF(MONTH(E$1)-MONTH(D$1)=0,IF($B2>=DAY(D$1),IF($B2<DAY(E$1),$C2,""),""),IF($B2<DAY(E$1),$C2,IF($B2>=DAY(D$1),$C2,"")))

It's ugly and complicated, but I tested it across not only the example spreadsheet I linked to above, but on my own budget management sheet.

Broken down for readability, it looks like this:

=IF(MONTH(E$1)-MONTH(D$1)=0,
   IF($B2>=DAY(D$1),
      IF($B2<DAY(E$1),
         $C2,
         ""
      ),
      ""
   ),
   IF($B2<DAY(E$1),
      $C2,
      IF($B2>=DAY(D$1),
         $C2,
         ""
      )
   )
)
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.