Search code examples
excelgoogle-sheetsspreadsheetgoogle-sheets-api

How to get a bill to populate with the amount, only if the particular payday falls exactly on or 6 days before the due date?

Google Sheets Budget Spreadsheet Snapshot

Google Sheets Budget Spreadsheet Snapshot2

=IF(AND(DATE(YEAR(EE$2),MONTH(EE$2)+IF($B6<DAY(EE$2),1,0),DAY($B6))>=EE$2,DATE(YEAR(EE$2),MONTH(EE$2)+IF($B6<DAY(EE$2),1,0),DAY($B6))<EE$2+14),470,”")

Using Google Sheets to make a budget (See attached image snapshots). I’m a little bit stuck on a complex spreadsheet formula (listed above). What I’m trying to do is get a bill to populate with the amount, only if the particular payday falls exactly on or 6 days before the due date. Here’s what I’m working with:

  • Column Headers: I have set these cells so that they auto-populate with the payday, which happens every week on Friday.
  • Row 6 and 7 are where I want the amount to show up. I’ve been having to put them in manually, but I’d like to automate it for the length of the bill (60 months)
  • Column B, cells (B6 & B7) are set with a plain text number corresponding to the day of the month it’s due (i.e. 5th of each month for the Ford). So B6 cell has number 5 in it.

If the due date for the Ford Car Payment is the 5th of every month, I need the number $470 to populate into the cell if the date above the column is the 5th of the month or minus 6 days from it. Because the bill doesn’t always fall on a payday, as in the in EE or EF Column, it needs to populate with $470 on the EE6 cell to make sure the bill is paid on time.

Essentially, I want to be able to paste the formula into the 6th row, and have it only populate on the pay date closest 5th of each month, but not after the 5th. If it returns 0 like on cell ED6, the cell should remain blank.

Any ideas how I can make this possible?

Solution 
  • =IF(ISNUMBER(MATCH($B5, ArrayFormula(DAY(C$3+{0,1,2,3,4,5,6})), 0)), 470, "")

    Using C3 as the start assuming your calendar days start at C3.

    The way this works is:

    1. ArrayFormula(C$3+{1,2,3,4,5,6}) Generates a synthetic Range of dates starting at the date in C3 and the week following
    2. ArrayFormula(DAY(C$3+{1,2,3,4,5,6})) just extracts the day's number of the month from all the dates. A date range from 2017-01-28 - 2017-02-03 would leave you with {28, 29, 30, 31, 01, 02, 03}
    3. MATCH($B5, <2>, 0) Will try to find the Due month-day in the month-day list we just generated and return the position or an error if it is not there.
    4. ISNUMBER(<3>) will be TRUE if the day is contained, otherwise false
    5. IF(<4>, 470, "") Prints the payment on the correct dates, of course you can change 470 to a cell reference like $ZZ5.

    You can drag this formula right and down