Auto Filter on Date Fields Problems

  • Thread starter Thread starter pw034
  • Start date Start date
P

pw034

I am creating a worksheet that will calculate cell phone bills for ou
employees.

Here some background - our company pays only a certain dollar amoun
for cell phone bills. I have created a workbook where the employe
downloads their cell phone bill from the website into a specifi
folder. I use the "import external data" option to link and format th
cell phone bill in excel. The employee will enter in numbers into th
worksheet that are normally considered "personal" numbers that the
call and it totals those minutes and assigns a % to the total bill.

The problem that I am having is I need to sort calls that are mad
after 5:00 PM and before 8:00 AM. The cell phone bill has both date
and times on it, so I thought it would be easy to use auto filter t
set the parameters and then it would calculate the number of minute
used during those times. I have tried everything, including changin
the date/time to an actual excel number - no luck. Using SUMIF
setting parameters - no luck.

Does anyone have suggestions on how I can set a filter to display onl
the calls that are made between 5:00PM and 8:00AM? I am not concerne
about the date, only the times.

Thank you in advance for any help!

Pau
 
Hi
try using a helper column (lets say column C) : If your dates are in
column A (starting in row 2) enter the following in C2:
=IF(OR(A1-INT(A1)>TIME(17,0,0),A1-INT(A1)<TIME(8,0,0)),"X","")
copy down for all rows (assumption column A are real Excel date/time
values)
- now use column C for filtering your desired data rows
 
Back
Top