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

Report •

#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-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! < 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.

MIKE

http://www.skeptic.com/


Report •
Related Solutions


Ask Question