Subtracting times

**acuratlmike** · 10-06-2009, 01:08 PM

Hi,

I would like to know what the formula is for subtracting 2 given times. For example...

A1 = 8/31/2009 05:17:25 PM and B1 9/1/2009 05:44:39 AM

I would like for cell C1 to calculate the total number of hours different from A1and B1..

Any help is greatly appreciated. Thanks!

**NBVC** · 10-06-2009, 01:10 PM

TRy:

=(B1-A1)*24

**ncmay** · 10-06-2009, 01:18 PM

Will this work for you?

=HOUR(B1-A1)&"Hours "&MINUTE(B1-A1)&"Minutes "&SECOND(B1-A1)&"Seconds"

**acuratlmike** · 10-06-2009, 01:16 PM

This is what it gives me 1/12/1900 10:53:36AM...is there a way to make it where it only shows, for example, 10 hours?

**NBVC** · 10-06-2009, 01:17 PM

Format that result cell as General.

If you just want hours...

=INT((B1-A1)*24)

formatted as General

**acuratlmike** · 10-06-2009, 01:23 PM

This WORKS!!! BIG Thank You to you guys!

**acuratlmike** · 10-06-2009, 01:29 PM

Another example...

A1 = 9/4/2009 01:28:02 PM B1 = 9/8/2009 08:59:21 AM

result I got was 19Hours 4Minutes 19Seconds

Isn't this wrong since there is a 3 working days difference between cell A1 and B1?

each day should of already been 24 hours.

**ncmay** · 10-06-2009, 01:31 PM

Modified Formula:

=DAY(B1-A1)&"Days "&HOUR(B1-A1)&"Hours "&MINUTE(B1-A1)&"Minutes "&SECOND(B1-A1)&"Seconds"

**daddylonglegs** · 10-06-2009, 02:09 PM

Hello ncmay,

If the period was longer than 31 days then you wouldn't get the correct number of days using DAY function, better to use INT, for example

=INT(B1-A1)&" Days "&TEXT(B1-A1,"hh:mm:ss")