Subtracting Hours Over 2 Days

Atomiq

New Member
Joined
Dec 8, 2005
Messages
2
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?
 

Some videos you may like

Excel Facts

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

22868

New Member
Joined
Apr 4, 2002
Messages
13
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
 

Atomiq

New Member
Joined
Dec 8, 2005
Messages
2
RE:

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

Watch MrExcel Video

Forum statistics

Threads
1,118,811
Messages
5,574,451
Members
412,595
Latest member
slim313
Top