Subtracting Hours Over 2 Days

Atomiq

New Member
I've been trying to find the proper formula to subtract time over two days. For example, hour many hours would it be if I started at 10:21PM and ended at 12:12AM? This should be 1 hour and 51 minutes. I'm not getting that, instead I get: -3:36

My start time in cell B4: 22:21:00
My end time in cell C4: 0:12:00

The formula is: 24*IF(B4>C4, C4-B4, 1+C4-B4)

Also, is it possible to have the result appear as 1.85 hours?

I'm using Excel 2002 v 10.2614

Any ideas?

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

22868

New Member
Atomiq,

This works for me:

Format cells B4 and C4 as time (1:30 PM)
Format desigated cel (example B8) as General and use

B8=24*IF(B4<C4; C4-B4; 1+C4-B4).

Input values should be entered as (for example) 23:12 to get 11:23 PM

I ran a couple of tests and it works fine.

Good luck

Pete on XL97

barry houdini

MrExcel MVP
You can use

=24*(C4-B4+(B4>C4))

Atomiq

New Member
RE:

Thank you guys very much for the help. I appreciate taking your time to work on this for me.

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,168,036
Messages
5,856,957
Members
431,841
Latest member
jaybeem

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.
Allow Ads at MrExcel

Which adblocker are you using?

Follow these easy steps to disable AdBlock

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

Follow these easy steps to disable AdBlock Plus

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