difference between two time
in [Functions]
|
Prev: Alternate Rounding Up & Down of .50 in a column
Next: extracting sheets on the basis of Row character
From: Radhakant Panigrahi on 2 Jun 2010 10:53 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
From: Luke M on 2 Jun 2010 10:59 XL can not display negative times/dates. You could "cheat" and do something like: =IF(B1<A1,"-","")&TEXT(B1-A1,"h:mm")&" hrs" -- Best Regards, Luke M "Radhakant Panigrahi" <rkp.gen(a)gmail.com> wrote in message news:F183380F-E9B1-4253-93CA-6B6AB964BD52(a)microsoft.com... > 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
From: Roger Govier on 2 Jun 2010 11:11 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" <rkp.gen(a)gmail.com> wrote in message news:F183380F-E9B1-4253-93CA-6B6AB964BD52(a)microsoft.com... > 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
From: Radhakant Panigrahi on 3 Jun 2010 08:41
Hi Luke, The formula helped me however if i have the B1<A1 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(B1<A1,"-","")&TEXT(B1-A1,"h:mm")&" hrs" > > -- > Best Regards, > > Luke M > "Radhakant Panigrahi" <rkp.gen(a)gmail.com> wrote in message > news:F183380F-E9B1-4253-93CA-6B6AB964BD52(a)microsoft.com... > > 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 > > > . > |