# Calculate percentage difference between dates with formula

#### chilly_bang

##### Board Regular
I have a table, like this:

Rich (BB code):
``````+------------+--------------+--------+
| Week       | Search query | Clicks |
+------------+--------------+--------+
| 2020-11-09 | keyword 1    | 3      |
+------------+--------------+--------+
| 2020-11-16 | keyword 1    | 3      |
+------------+--------------+--------+
| 2020-11-09 | keyword 2    | 7      |
+------------+--------------+--------+
| 2020-11-16 | keyword 2    | 14     |
+------------+--------------+--------+``````

How can i calculate the percentage difference of clicks between the latest and earliest date with a formula?

Anyhow like this: IF B3=(B3-1) & A3>(A3-1) → iferror(100-(c3*100/(C3-1));0)

I've tried it with pivot - it works, but after data refresh my pivot gets broken.

Last edited by a moderator:

#### chilly_bang

##### Board Regular
For example, in the first two records for "abmahnschutz", how do you get to "100" from those two records?

Not 100 - but 50.

This is the original:

Code:
``````+------------+-----------------------------------------------------+--------+
| Week       | Search query                                        | Clicks |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | abmahnschutz                                        | 4      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | abmahnschutz                                        | 8      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | abmahnschutz versicherung                           | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | abmahn versicherung                                 | 3      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | ambulante zusatzversicherung ohne gesundheitsfragen | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | ambulante zusatzversicherung ohne wartezeit         | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | app drive x                                         | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | autoversicherung                                    | 6      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | autoversicherung                                    | 6      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | autoversicherungen                                  | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | bauzeitversicherung                                 | 6      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | beamte altersvorsorge                               | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | beitragsentlastung                                  | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | beitragsentlastungstarif                            | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | beitragsentlastungstarif                            | 2      |
+------------+-----------------------------------------------------+--------+
| 2020-11-09 | betriebsunterbrechungsversicherung                  | 3      |
+------------+-----------------------------------------------------+--------+
| 2020-11-16 | betriebsunterbrechungsversicherung                  | 10     |
+------------+-----------------------------------------------------+--------+``````

If i display the original table with dates as columns, so it looks like:

Code:
``````+-----------------------------------------------------+------------+------------+
| Search query                                        | 09.11.2020 | 16.11.2020 |
+-----------------------------------------------------+------------+------------+
| abmahn versicherung                                 |            | 3          |
+-----------------------------------------------------+------------+------------+
| abmahnschutz                                        | 4          | 8          |
+-----------------------------------------------------+------------+------------+
| abmahnschutz versicherung                           |            | 2          |
+-----------------------------------------------------+------------+------------+
| ambulante zusatzversicherung ohne gesundheitsfragen |            | 2          |
+-----------------------------------------------------+------------+------------+
| ambulante zusatzversicherung ohne wartezeit         | 2          |            |
+-----------------------------------------------------+------------+------------+
| app drive x                                         |            | 2          |
+-----------------------------------------------------+------------+------------+
| autoversicherung                                    | 6          | 6          |
+-----------------------------------------------------+------------+------------+
| autoversicherungen                                  | 2          |            |
+-----------------------------------------------------+------------+------------+
| bauzeitversicherung                                 | 6          |            |
+-----------------------------------------------------+------------+------------+
| beamte altersvorsorge                               |            | 2          |
+-----------------------------------------------------+------------+------------+
| beitragsentlastung                                  | 2          |            |
+-----------------------------------------------------+------------+------------+
| beitragsentlastungstarif                            | 2          | 2          |
+-----------------------------------------------------+------------+------------+
| betriebsunterbrechungsversicherung                  | 3          | 10         |
+-----------------------------------------------------+------------+------------+``````

So the date difference will be calculated with `=IFERROR(100-(B2*100)/(C2);0)` and the final result i want will look like:

Code:
``````+-------------------------------------------------------+--------------------------------+
| Search query                                          | Click Difference between dates |
+-------------------------------------------------------+--------------------------------+
| abmahn versicherung                                   | 100                            |
+-------------------------------------------------------+--------------------------------+
| abmahnschutz                                          | 50                             |
+-------------------------------------------------------+--------------------------------+
| abmahnschutz versicherung                             | 100                            |
+-------------------------------------------------------+--------------------------------+
| ambulante zusatzversicherung ohne   gesundheitsfragen | 100                            |
+-------------------------------------------------------+--------------------------------+
| ambulante zusatzversicherung ohne   wartezeit         | 0                              |
+-------------------------------------------------------+--------------------------------+
| app drive x                                           | 100                            |
+-------------------------------------------------------+--------------------------------+
| autoversicherung                                      | 0                              |
+-------------------------------------------------------+--------------------------------+
| autoversicherungen                                    | 0                              |
+-------------------------------------------------------+--------------------------------+
| bauzeitversicherung                                   | 0                              |
+-------------------------------------------------------+--------------------------------+
| beamte altersvorsorge                                 | 100                            |
+-------------------------------------------------------+--------------------------------+
| beitragsentlastung                                    | 0                              |
+-------------------------------------------------------+--------------------------------+
| beitragsentlastungstarif                              | 0                              |
+-------------------------------------------------------+--------------------------------+
| betriebsunterbrechungsversicherung                    | 70                             |
+-------------------------------------------------------+--------------------------------+``````

So how i come from the original table to the wanted result with a formula?

I'm very bad explainer. @Joe4 you are the hero on your trying to understand me.

Last edited:

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

#### Joe4

Do you only have the two dates?
If so, then why not just use the original data table, where you have two columns of dates?
Then, you could just put this formula in, which would only return a value if both date columns are populated for that row:
Excel Formula:
``=IF(AND(B2<>"";C2<>"");100-(B2*100)/C2;"")``

#### chilly_bang

##### Board Regular
Yes, there are always only two dates. Sure, your last method would be very handy but my original table doesn't have two columns for dates - in original table dates are all in the same column.

I add a file, where original table is located in the first tab, , the tab "Pivot" contains pivoted original data and calculation of click difference between dates, and the third tab "result" contains the table i want to achieve without pivot. Here is the example file.

#### Joe4

I do not know of a good approach without using VBA. I rarely use Pivots myself, and would tend to use Micosoft Access to do this (as I am much better with Microsoft Access queries than with Pivots).

In Excel, I would probably use VBA to change the structure of your data to look like the two-column date layout that you showed.
If that is an acceptable approach to you, let me know, and I can come up with VBA code do that.

Replies
24
Views
182
Replies
1
Views
60
Replies
1
Views
82
Replies
4
Views
365
Replies
3
Views
178

1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back