Difference in Time hour what formula to use in excel?

[Sunita23]

Hi

Cell A1 & Cell B1 is having time format of 24hrs, h:mm,

I have put the data in A1 as 23.30 pm & in B1 1.30 am, differenc
between these two time is 2 hrs, .I have tried all the formula hour
time etc but didn't get the correct value.

Please suggest me the right formula to use here.

Thank you
Sunit

 

[Claus Busch]

Hi Sunita,

Am Fri, 19 Oct 2012 10:55:41 +0000 schrieb Sunita23:

Cell A1 & Cell B1 is having time format of 24hrs, h:mm,

I have put the data in A1 as 23.30 pm & in B1 1.30 am, difference
between these two time is 2 hrs, .I have tried all the formula hour,
time etc but didn't get the correct value.

try:
=MOD(B1-A1,1)


Regards
Claus Busch

 

[Claus Busch]

Hi Sunita,

Am Fri, 19 Oct 2012 15:48:48 +0200 schrieb Claus Busch:

=MOD(B1-A1,1)

if you want an integer instead of time then:
=24*MOD(B1-A1,1)
and format the cell with the result as "General"


Regards
Claus Busch

 

[Claus Busch]

Hi Kevin,

Am Sat, 20 Oct 2012 06:35:46 +0000 schrieb

Or just add 1: =(1+B1)-A1

the MOD formula works with dayshift *and* with nightshift.
With dayshift your formula has 24 hours to many. It *shows* the real
result but if you format the cell [h]:mm you will see it.


Regards
Claus Busch

 

[Kevin@Radstock]

Yes mate, I am well aware of that! As you well know there are many way
to skin a cat. So for the OP I was pointing out another option!
Another version, bit longer then the MOD: =IF(B1<A1,1+B1,B1)-A1

Hi Kevin,

Am Sat, 20 Oct 2012 06:35:46 +0000 schrieb

Or just add 1: =(1+B1)-A1-

the MOD formula works with dayshift *and* with nightshift.
With dayshift your formula has 24 hours to many. It *shows* the real
result but if you format the cell [h]:mm you will see it.


Regards
Claus Busch