calculating difference in time formula

[hotdogs1999]

hello,

looking to calculate the difference in time for 2 cells, and then reducing that total by the time in a 3rd cell.

e.g cell B4 is '7.30' and B5 is '17.30' and cell B6 is '0.30'

i want to find the difference between B4 and B5, and then remove the B6 value off that total.

fyi time format is hour.minute (don't need seconds in the format)

Thanks in advance

 

[jasonb75]

Try

=MOD(B5-B4,1)-B6

MOD is not strictly necessary but it will prevent errors when times cross midnight.

 

[hotdogs1999]

thanks.
times won't ever go over midnight so that won't matter

your formulas coming up with a random value

number format for B5 and B4 is 'Time', and B6 is 'Number' - if that affects how the formula works?

 

[jasonb75]

I thought that it was a typo in your post, 7.30 17.30 and 0.30 are not times, 7:30 17:30 and 0:30 are times (see the difference?).

7.30 formatted as time is 7 days 7 hours and 12 minutes, mixed formatting is asking for trouble all cells need to be the same format.

 

[hotdogs1999]

yip i notice that. only problem is you can't enter 30 mins as a time, as entering 0:30 changes it to 12:30am which is a time not a 'period of time'

 

[jasonb75]

Not if you format as 24 hour. Table below uses standard 24 hr time format of hh:mm, you can change that to h:mm for A6 if you want to drop the first zero.

Book2
	A
4	07:30
5	17:30
6	00:30
7	09:30
Sheet6

Cell Formulas
Range	Formula
A7	A7	=MOD(A5-A4,1)-A6

 

[hotdogs1999]

thanks that works good