Calculating the Percentage between Dates in Google Sheets

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:

Percentage between Dates in Google Sheets - Example

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%})
Elapsed Days and Inline Chart in Google Sheets

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:

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Lookup Previous Values Dynamically in Excel and Google Sheets

Sometimes, you may want to look up the previous values relative to the current...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

Lookup Values Under Categories in Excel and Google Sheets

We can use a combination of XLOOKUP and VLOOKUP in both Excel and Google...

Extract Items Under a Category from the Same Column in Excel

In Excel, you can use a combination of the XLOOKUP and DROP functions to...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.