# Time variance

#### reganshaw

##### Board Regular
I'm sure this is an easy one but I just can't quite figure it out...

Cell A1 = 20:00
Cell A2 = 20:10

I want cell A3 to show the variance between the 2 in whole minutes. ie;

Cell A3 = +0:17

Can you help me with the formula and cell formatting?

### Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Hello, reganshaw,

try this
=(A2-A1)*24

(perhaps you still want to round, else you can change the numberformat to 2 decimals)

kind regards,
Erik

time-calculations are performed in "days" so 20:10 - 20:00 = a fraction of a day (about 0.0069)

Thanks Erik !

you're welcome !!

I just saw a problem: my experience with timecalculations is limited
When the calculation needs to go "over midnight" you will have a problem.

so you would better use
Code:
``=(A2-A1+(A2<A1))*24``

best regards,
Erik

Hi reganshaw:

Seeing 0:17 in your post promted me to say you may want to look at the following as well ...
y051124h1.xls
ABCDEFG
1
220:10-20:00=0.17 hours=10 minutes
3
Sheet2

Thanks Erik & Yogi.

The revised formula works well however;

In my spreadsheet, cell A1 holds the Scheduled time. Cell A2 holds the Actual time. With the revised formula if A1 = 00:00 and A2 = 23:30 (event took place 30 minutes early) the formula returns a value of 23.5

With the first formula you provided Erik, I can resolve the "past mindight" issue by continuing with the 24 hour clock. For example 01:00 would be data entered into cell A2 as 25:00. This returns a value of 1.0 if the scheduled time was 00:00 and I can still return a negative value if the Actual time is earlier than the scheduled time.

Long story short... the first solution works perfect!

Thanks again to you both.

Replies
1
Views
77
Replies
44
Views
1K
Replies
1
Views
344
Replies
1
Views
122
Replies
2
Views
949

1,196,329
Messages
6,014,679
Members
441,835
Latest member
rthomas268

### 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