# Need IF formula for Time

November 19, 2010 at 09:14:00
Specs: Windows XP, duo core
 I printed a production table from Access and converted it to Excel. Three columns: Case Nbr, Billed Time, Employee.The case billed column is in military time hh:mm. I created a new column in excel to give a time difference between each case that is billed and shows time difference in hh:mm. I want to use this column (time difference) and make a new column with a greater or less than formula. I would like the formula to do the following: If the time difference is less than :02 I would like the case nbr for that row to appear in the cell, if the time difference is more than :15 I would like that case nbr for that row to appear in the cell. If the time difference falls in between :02 and :15 i would like the cell to stay blank or a zero put into the cell. Thanks

See More: Need IF formula for Time

#1
November 19, 2010 at 11:41:38
 The format for what you are looking for should resemble this:Let's assume the case nbr is in A1 and the "time difference" is in D1. Try this:=IF(OR(MINUTE(D1)<2,MINUTE(D1)>15),A1,"")P.S. I assume that between :02 and :15 includes :02 and :15.Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •

#2
November 19, 2010 at 13:29:49
 I created a new column in excel to give a time difference between each case that is billed and shows time difference in hh:mm.A caution, if your time calculations cross over midnight you may experience some problems.I'm guessing that your formula in Column D is somthing like: =B3-B2With DerbyDad03s formula in E3Your data looks like:``` A B C D E 1) Case Nbr Billed Time Employee. Differnece 2) 001 23:58:00 3) 002 0:05:00 -0.995138889 #NUM! < Formula ```You will notice that the time difference is expressed as a negative number, which will force DerbyDad03s formula to error out with a #NUM! error.The simple correction is to add a date to your Billed Time.MIKEhttp://www.skeptic.com/

Report •

Related Solutions