I need a formula please

[TC123]

Hello,

I have created a spreadsheet which is recording start and finish times. The planned start time is 9:00 and the planned finish time is 12:30. In A2 someone has recorded the start time to be 09:05 and in column B2 they have recorded the finish time to be 12:35. In column C2 I need a formula that will work out the difference in time from the planned times (in minutes). For example, with the new times we can see we have gone 10 minutes over in total so in column C2, I would like the answer to be 00:10. Can anyone help me please?

Thank you in advance

 

[VoG]

Try

=(B2-A2)-("12:30"-"09:00")

 

[anglais428]

In cell C2 enter

=(B2-$B$1)+(A2-$A$1)

Assuming 09:00 is in A1, 12:30 is in B1, 09:05 is in A2, 12:35 is in B2

 

[TC123]

Thank you for that but I'm afraid it's not working. I am getting a message from Excel correcting the formula by adding another bracket on the end and when I accept the correction I am just getting #### in the cell. Any ideas?

 

[j_latendre]

That should be fine, now just adjust your column width, it is just telling you there isn't enough room to show the full number.

Jesse

 

[VoG]

THis is what I get but it will fail if the difference is negative

Excel Workbook
	A	B	C
2	09:05	12:45	00:10
Sheet5

Maybe

Excel Workbook
	A	B	C
2	09:05	12:00	-35
Sheet5

 

[TC123]

Thanks Anglais, that's worked perfectly!

 

[TC123]

Thanks to everyone. Could somebody advise what I should do if the number is going to be negative as I'd also like to display this in column C aswell. So, for example, if the start time was 08:55 and finish time is 11:55. Thanks

 

[VoG]

See my second formula here http://www.mrexcel.com/forum/showpost.php?p=2653760&postcount=6

You would need to display as Number (of minutes) rather than hh:mm.