Time variance

[reganshaw]

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?

Thanks in advance.

 

[erik.van.geit]

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

EDIT: if you wanted some more info
time-calculations are performed in "days" so 20:10 - 20:00 = a fraction of a day (about 0.0069)

 

[reganshaw]

Thanks Erik !

 

[erik.van.geit]

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

=(A2-A1+(A2<A1))*24

best regards,
Erik

 

[Yogi Anand]

Hi reganshaw:

Seeing 0:17 in your post promted me to say you may want to look at the following as well ...

y051124h1.xls
	A	B	C	D	E	F	G
1
2	20:10	-	20:00	=	0.17 hours	=	10 minutes
3
Sheet2

 

[reganshaw]

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.