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, difference 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
Sunita

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Hi

Or just add 1: =(1+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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Yes mate, I am well aware of that! As you well know there are many ways 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