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 :15includes:02 and :15.

Posting Tip:Before posting Data or VBA Code, read thisHow-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.

MIKE

Ask Your Question

Weekly Poll

Do you think Microsoft can save the Surface Book lineup?

Discuss in The Lounge

Poll History