Calculating difference of time in one cell

[LeeBillington]

Hi guys,
is there anyway of finding out a calculation of a time difference in one cell? so for example if A1 = 13:30 - 17:15 , Is there a way in B2 to find out the difference between that time?

[JeteMc]

Try:

Please Login or Register  to view this content.

Format cell B2 as: h:mm;@

[LeeBillington]

Thank you so much! it worked, even just formatting it as time worked!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

[LeeBillington]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.

Just a quick question, is there a way to implement iferror if it's blank to return nothing into the formula

[JeteMc]

You could modify the formula as follows, however it will take the time to do the calculation first, so I think that the second modification would be preferable:

Please Login or Register  to view this content.

You could modify the formula as follows so that if A1 is blank B2 will also be blank and it won't proceed to attempt the calculation:

Please Login or Register  to view this content.

Let us know if you have any questions.

[LeeBillington]

That worked perfectly thanks a lot!

Going to get a bit annoying, but one more question haha..

so the answer is in B1, say i used the formula down to B45, and had multiple 3.45 from calculating 03:30 - 07:15.. how would i sum up all the hours? because right now its giving incorrect answers. any idea on a formula i could use to calculate it? Normal =SUM is just giving me 45, because theres 45 cells

[JeteMc]

The cell with the summation should be custom formatted [h]:mm
To custom format select the cell with the summation and simultaneously press the Ctrl and 1 keys, custom will be the last option on the number tab.
Either type [h]:mm in the 'Type' window or select that format from the list below that window.
Let us know if you have any questions.

[LeeBillington]

That did work, thanks for all your help!

[JeteMc]

You're Welcome. Thank You for the feedback and for marking the thread as 'Solved'. I hope that you have a blessed day.