Click here for important information about Computing.net.
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
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.
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-B2
With DerbyDad03s formula in E3
Your 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! < FormulaYou 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.
Ad Choices