A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

difference between two time



 
 
Thread Tools Display Modes
  #1  
Old June 2nd, 2010, 03:53 PM posted to microsoft.public.excel.worksheet.functions
Radhakant Panigrahi
external usenet poster
 
Posts: 43
Default difference between two time

Hi,

i have the times in 12:00 PM in cel A1 and 9:30 AM in B1 and i just want to
calculate the difference between B1-A1 which is -2.30 hrs, but in excel it is
not coming only A1-B1 is coming which is +ve 2.30 hrs

can anybody help me.

regards,
radha
Ads
  #2  
Old June 2nd, 2010, 03:59 PM posted to microsoft.public.excel.worksheet.functions
Luke M[_4_]
external usenet poster
 
Posts: 451
Default difference between two time

XL can not display negative times/dates. You could "cheat" and do something
like:
=IF(B1A1,"-","")&TEXT(B1-A1,"h:mm")&" hrs"

--
Best Regards,

Luke M
"Radhakant Panigrahi" wrote in message
...
Hi,

i have the times in 12:00 PM in cel A1 and 9:30 AM in B1 and i just want
to
calculate the difference between B1-A1 which is -2.30 hrs, but in excel it
is
not coming only A1-B1 is coming which is +ve 2.30 hrs

can anybody help me.

regards,
radha



  #3  
Old June 2nd, 2010, 04:11 PM posted to microsoft.public.excel.worksheet.functions
Roger Govier[_8_]
external usenet poster
 
Posts: 338
Default difference between two time

Hi

You can have negative times if you switch to the 1904 Date system, but
beware of any other dates you may have on the sheet altering.

Alternatively, if you can accept decimal times then
=(A1-B1)*24
Format the cell as General.
--

Regards
Roger Govier

"Radhakant Panigrahi" wrote in message
...
Hi,

i have the times in 12:00 PM in cel A1 and 9:30 AM in B1 and i just want
to
calculate the difference between B1-A1 which is -2.30 hrs, but in excel it
is
not coming only A1-B1 is coming which is +ve 2.30 hrs

can anybody help me.

regards,
radha

__________ Information from ESET Smart Security, version of virus
signature database 5166 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 5166 (20100602) __________

The message was checked by ESET Smart Security.

http://www.eset.com



  #4  
Old June 3rd, 2010, 01:41 PM posted to microsoft.public.excel.worksheet.functions
Radhakant Panigrahi
external usenet poster
 
Posts: 43
Default difference between two time

Hi Luke,

The formula helped me however if i have the B1A1 then i am facing issue...

I need the below format the column A is fixed with 12 PM

12:00 PM 9:30 AM -2:30 hrs
12:00 PM 9:30 PM 9:30 hrs


Regards,
Radha
"Luke M" wrote:

XL can not display negative times/dates. You could "cheat" and do something
like:
=IF(B1A1,"-","")&TEXT(B1-A1,"h:mm")&" hrs"

--
Best Regards,

Luke M
"Radhakant Panigrahi" wrote in message
...
Hi,

i have the times in 12:00 PM in cel A1 and 9:30 AM in B1 and i just want
to
calculate the difference between B1-A1 which is -2.30 hrs, but in excel it
is
not coming only A1-B1 is coming which is +ve 2.30 hrs

can anybody help me.

regards,
radha



.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:31 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright 2004-2020 OfficeFrustration.
The comments are property of their posters.