time problems
I have a spread sheet and in this sheet i have a set time that it takes to do
a run ie 01:30 1 hour 30 mins (A1) then i have another cell that i input a time (A2) and a 3rd cell that has an if function that says =if( a20, a2-a1,"") this works fine untill the time difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get ###### instead of 23:30, in lotus 123 it worked fine but excel seems to have a problem with this, any ideas what i am doing wrong ? |
time problems
=MOD(A2-A1,1)
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stan Halls" wrote in message ... I have a spread sheet and in this sheet i have a set time that it takes to do a run ie 01:30 1 hour 30 mins (A1) then i have another cell that i input a time (A2) and a 3rd cell that has an if function that says =if( a20, a2-a1,"") this works fine untill the time difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get ###### instead of 23:30, in lotus 123 it worked fine but excel seems to have a problem with this, any ideas what i am doing wrong ? |
time problems
Hi!
Try this: =IF(A20,A2-A1+(A2<A1),"") Format the cell as TIME 13:30 or CUSTOM h:mm One thing of note, you're testing cell A2 against 0. What if A2 = 12:00 AM which is equal to zero? Maybe you want this instead: =IF(ISNUMBER(A2),A2-A1+(A2<A1),"") Biff "Stan Halls" wrote in message ... I have a spread sheet and in this sheet i have a set time that it takes to do a run ie 01:30 1 hour 30 mins (A1) then i have another cell that i input a time (A2) and a 3rd cell that has an if function that says =if( a20, a2-a1,"") this works fine untill the time difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get ###### instead of 23:30, in lotus 123 it worked fine but excel seems to have a problem with this, any ideas what i am doing wrong ? |
time problems
Bob
I am not very good at this excel , is the =mod function able to be used within an if function so that if there is no time in cell A2 then i get a blank cell "Bob Phillips" wrote: =MOD(A2-A1,1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stan Halls" wrote in message ... I have a spread sheet and in this sheet i have a set time that it takes to do a run ie 01:30 1 hour 30 mins (A1) then i have another cell that i input a time (A2) and a 3rd cell that has an if function that says =if( a20, a2-a1,"") this works fine untill the time difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get ###### instead of 23:30, in lotus 123 it worked fine but excel seems to have a problem with this, any ideas what i am doing wrong ? |
time problems
Bob
have sorted it out, thanks for the help to all of you that posted, =IF(ISNUMBER(a2),MOD(a2-a1,1),"") this worked for me "Bob Phillips" wrote: =MOD(A2-A1,1) -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Stan Halls" wrote in message ... I have a spread sheet and in this sheet i have a set time that it takes to do a run ie 01:30 1 hour 30 mins (A1) then i have another cell that i input a time (A2) and a 3rd cell that has an if function that says =if( a20, a2-a1,"") this works fine untill the time difference goes back past midnight, if A2 = 01:00 and A1= 01:30 then i get ###### instead of 23:30, in lotus 123 it worked fine but excel seems to have a problem with this, any ideas what i am doing wrong ? |
All times are GMT +1. The time now is 07:18 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com