No doubt, counting days since previous payment is one of the most occurring tasks to do in Google Sheets.
We can simplify it using an array formula. Here is how.
Since date difference is a common task, there is, of course, a dedicated function in Spreadsheets. Google Sheets is no exception.
In Google Sheets, there are two functions for this purpose, and both will work with the ArrayFormula too. But there is one limitation. I will come to that later.
I have a table like this in Sheets.
First Column (Column A) – Date column to record (for entering) transaction dates.
Second Column (Column B) – Amount due.
Third Column (Column C) – Amount paid.
Fourth Column (Column D) – Running balance column (for this, I already have an array formula, I will leave the link after a few paragraphs below).
Column E – Require an array formula to return the counting of days since the previous payment in each row.
Why Do We Require a Custom Formula?
Column C is the payment column, and you can see the corresponding payment dates in column A.
As you can see, there are blank cells (no payments) between payments.
If there are no other dates between the payment dates (if all the payments are in adjoining rows), we can use the following two dedicated functions.
1. DAYS.
2. DATEDIF.
Note:- To learn these functions, please check How to Utilize Google Sheets Date Functions [Complete Guide].
We can also use the subtraction operator to return days since the previous payment date in Google Sheets.
Another option is the Query DATEDIFF, but that won’t suit our table.
Array Formula to Count Days Since Previous Payment in Google Sheets
Let’s go to my solution step-by-step. Before that, for the running balance, I have used the below formula in cell D1.
={"Outstanding (Running Balance)";ArrayFormula(if(len(A2:A),(SUMIF(ROW(A2:A),"<="&ROW(A2:A),B2:B)-SUMIF(ROW(A2:A),"<="&ROW(A2:A),C2:C)),))}For the formula explanation, please read this guide – How to Calculate Running Balance in Google Sheets.
My array formula that counts days since the previous payment is relatively simple.
The formula is lengthy because I have replaced some parameters with expressions. So it’s better to learn the formula step by step.
You may please find the steps under subtitles below.
Formula to Return the Row Numbers of the Amount Paid Dates
Here is our first step.
As you can see from the highlighted rows, the row numbers to extract are 3, 4, 6, 7, 8, 9, 11, and 13.
We can get it by filtering row numbers A2:A if the values in C2:C are > 0.
Step_1_formula
=query(filter(row(A2:A),C2:C>0),"Select * offset 1",0)We don’t want the row numbers of the first payment date as there is no point in finding the date difference of any other date as it’s the first payment.
That’s why I have used the Query along with Filter. It offsets 1 row.
I have inserted the above formula in cell G1. Please see the screenshot in the second step below.
Creating Two Date Columns from One Payment Column
This second step is the important one in writing the array formula to counting days since the last payment in Google Sheets.
This step will help us to return the date difference from the last payment.
The purpose of the first step is to distribute the step 2 results to corresponding rows. We will learn that later on.
There are two formulas.
In cell H1;
Step_2.1_formula
={FILTER(A2:A,C2:C>0);0}In cell I1;
Step_2.2_formula
={0;filter(A2:A,C2:C>0)}
The first and second formulas (H1 and I1 formulas) filter the cell range A2:A if the amount paid (C2:C) is greater than 0.
The first formula inserts a row that contains 0 at the bottom, and the second formula inserts 0 at the top of the row.
You can refer to the above screenshot to understand it (see the arrow marks).
Here the logic lies in the formula for counting days since the previous payment in Google Sheets. Let’s detail that part in the next step.
Array Formula to Count Days Since Previous Payment Dates in Google Sheets
It is our third step, and only one more step is left.
Deduct the column I dates from the column H dates to get the days since the last payments.
But we don’t want the date difference in cells J1 and J9 (please see the image below) because each row has a blank cell (I1 and H9).
So we can use this generic formula.
if(step_2.1_formula*step_2.2_formula,step_2.1_formula-step_2.2_formula,)Corresponding Formula in Cell J1 (Step_3_draft_formula)
=ArrayFormula(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},))Explanation and Result
If the multiplication of step_2.1_formula with step_2.2_formula returns a value greater than 0, substract step_2.2_formula result from the result of step_2.1_formula.
As you can see, if you consider column G, there are two blank cells in column J. The cells in question are J1 and J9.
If I directly modify the above formula, some of you may be confused. So, I am going to edit it based on the generic formula below.
=filter(step_3_draft_formula,step_3_draft_formula<>"")So the formula to use in J1 is as follows.
Step_3_formula:
=filter(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},),if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},)<>"")Vlookup to Arrange Days Since Previous Payment to Corresponding Rows
In this final step, let’s combine all the pieces.
We have now all the required details.
That means, we have the count of the days of since previous payment in column J and corresponding row numbers in column G.
We can combine them to create a table (range) for Vlookup. So that, using row numbers A2:A as the search keys, we can distribute the days.
Generic Formula to use in cell E2:
=ArrayFormula(IFNA(vlookup(row(A2:A),{step_1_formula,step_3_formula},2,0)))This formula part {step_1_formula,step_3_formula}, combines the columns G and J.
Vlookup tries to match all the row numbers in A2:A in this table and returns the result from the second column.
Here is the formula form.
=ArrayFormula(IFNA(vlookup(row(A2:A),{query(filter(row(A2:A),C2:C>0),"Select * offset 1",0),filter(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},),if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},)<>"")},2,0)))Final Formula:
We want the formula in cell E1, not in cell E2. So let’s insert the title “Days Since Previous Payment” within it.
={"Days Since Previous
Payment";ArrayFormula(IFNA(vlookup(row(A2:A),{query(filter(row(A2:A),C2:C>0),"Select * offset 1",0),filter(if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},),if({filter(A2:A,C2:C>0);0}*{0;filter(A2:A,C2:C>0)},{filter(A2:A,C2:C>0);0}-{0;filter(A2:A,C2:C>0)},)<>"")},2,0)))}The above is the final formula. Now you can remove all the helper column formulas in the range G1:J.
That’s all. Thanks for the stay. Enjoy!
Resources:
