Excel Data Differences: Understanding Variances

Difference between two dates

The easiest way to calculate date difference is to use DATEDIF function. This is dedicated function to calculate difference between two dates.

=DATEDIF(B1,B2,”D”) – formula to calculate days between dates

=DATEDIF(B1,B2,”M”) – formula to calculate months between dates

=DATEDIF(B1,B2,”Y”) – formula to calculate years between dates

There is also a possibility to calculate quarters between dates.

To learn more we have also tutorial dedicated to date calculations.

Difference between two columns

You have two columns and would like to compare them. This is possible with Excel.

First thing you can check is if the cells are exactly the same. It is a Excel formula which easily spot the difference.

=IF($A2=$B2,”MATCH”,”DIFFERENCE SPOTTED”)

We used IF function to write the result down.

The second way is to check if the cell value exists in the other column.
Vlookup function knowledge will help to build such Excel formula:

=IFERROR(VLOOKUP($A2,$B$2:$B$14,1,FALSE),”NOT FOUND”)

You can also compare columns for duplicates.

How do I compare time differences in Excel?

Good question! There is no dedicated Excel function for time difference. Instead of that just use simple subtraction and properly configure your data formatting.

See the picture. I used simple Excel subtraction.

=TEXT($B2-$A2,”HH:MM:SS”)

The trick is to change data formatting. Instead of doing it manually. I used TEXT function to do that. I will get the result as hours, minutes and seconds.

Please check laso time math tutorial.

Percentage difference

To calculate the percentage difference you need to remember to subtract 1. Excel formula will be:

=$B2/$A2-1

Subtracting 1 is needed to see the percentage difference.

More on how to calculate percent change in excel here.