In this post, you can learn to calculate the percentage between two dates in Google Sheets.

You may find this calculation very useful in some specific scenarios. What are those scenarios?

For example, you can use this method for calculating the percentage of time/days elapsed till today or as on a particular date from a start and end date.

With that data in hand, we can plot an in-cell bar chart the visualizes the days elapsed.

Other than this, in this tutorial, you can learn two more percentage calculations. They are;

1. How to calculate the percentage of days allocated to each task.
2. How to add ‘n’ percentage to start date.

You can find these two tips under the “Additional Tips” section in the last part of this tutorial.

First of all, we will start how to calculate the percentage between two dates in Google Sheets.

How to Calculate the Percentage between Dates in Google Sheets

Sample Data:

I have five tasks in column A and their start and end dates in columns B and C.

I want to calculate the percentage of days elapsed in each row. Let’s see how to do that in Google Sheets.

Steps to Follow to Calculate the Percentage between Two Dates

1. Insert the below formula in cell D1 to return the current date in that cell.

=today()

If you want to calculate the percentage between two dates based on any particular date, enter that date in cell D1 instead of the today() formula.

2. We can use DATEDIF or DAYS functions to write the formula in D4 to return the percentage of elapsed days.

Must Check: How to Utilise Google Sheets Date Functions [Complete Guide]

Formula # 1

=to_percent((days($D$1,B4)+1)/(days(C4,B4)+1))

Formula # 2

=TO_PERCENT((datedif(B4,$D$1,"D")+1)/(datedif(B4,C4,"D")+1))

The result of these formulas will be different at one point!

You can see the result returned by the first (DAYS) formula in column D on the image above.

If we use the second formula, i.e., DATEDIF, then the % value in D8 will be the error #NUM because it won’t return a negative percentage.

If you like array formulas to calculate the percentage between dates in Google Sheets, then empty D4:D8 and use either of the below ones.

Formula # 3

=ArrayFormula(to_percent((days($D$1,B4:B8)+1)/(days(C4:C8,B4:B8)+1)))

Formula # 4

=ArrayFormula(to_percent((datedif(B4:B8,$D$1,"D")+1)/(datedif(B4:B8,C4:C8,"D")+1)))

We will use formula # 1 (non-array) to calculate the percentage of elapsed days and then plot the in-cell bar chart. You can find the details below.

Elapsed Days and In-Cell Bar Chart (Sparkline)

We can use the above percentage values in D4:D8 to plot a Sparkline chart that shows the elapsed days in visual form.

Before that, in D4:D8, we should limit the percentage values between 0 and 100.

Generic Formula: =to_percent(median(0%,100%,formula_1))

Replace formula_1, the formula that returns the percentage between dates (elapsed days in %) in Google Sheets, with the corresponding formula.

Here is that one (the bold part).

=to_percent(median(0%,100%,to_percent((days($D$1,B4)+1)/(days(C4,B4)+1))))

Enter this formula in cell D4 and drag it down to copy.

Then let’s plot the in-cell bar chart for the elapsed days. For that, use the following Sparkline formula in cell E4. Copy it down.

=sparkline({D4},{"charttype","bar";"color1","blue";"max",100%})

Additional Tips

In our example, with percent distribution, we can distribute the total project days among the days allocated for each task. Calculating the percentage of the total is pretty simple.

What you want to do is divide the duration of each task by the total project duration (in days).

In cell F4, you may insert the following formula and copy-paste it down.

=TO_PERCENT(days(C4,B4)/ arrayformula(sum(days($C$4:$C$8,$B$4:$B$8))))

When you total the result, you will get 1, which means 100%

Here is one more tip.

Do you know how to add a certain percentage to a start date and get a new start date?

For that, we require two dates – a start date (B4) and an end date (C4).

=to_date(INT(B4+days(C4,B4)*25%))

As per the above sample data, the formula will return the date 15/06/2021.

That’s all about how to calculate the percentage between two dates in Google Sheets.

Thanks for the stay. Enjoy!

Resources:

• How to Create Percentage Progress Bar in Google Sheets.
• Percent Distribution of Grand Total in Google Sheets Query.
• Percentage Change Array Formula in Google Sheets.
• How to Round Percentage Values in Google Sheets.