Count number of days that fall on a weekend or in a time range?

  • Thread starter Thread starter Gregg Hill
  • Start date Start date
G

Gregg Hill

Hello!

I am trying to reconcile my cell phone bill. I have copied and pasted it
into Excel 2003. I want to find all entries that fall on Saturday or Sunday,
cut/paste that portion into another area of the sheet, then sum the number
of minutes used on those days.

I also want to find all entries that fall between 9:00PM and 6:00AM in the
remainder of the bill and sum those entries.

Any suggestions?

Thank you for helping!

Gregg Hill
 
Hi
lets assume the following:
- column A: your dates as Excel date format (check this with
=ISNUMBER(A1) -> should return TRUE)
- column B: the starting time as real Excel time
- column C: the minutes (as integers)

Try the following formula
=SUMPRODUCT(--(WEEKDAY(A1:A100)<3),C1:C1000)

For the second question I would assume the following:
=SUMPRODUCT(--(WEEKDAY(A1:A100)>=3),--((B1:B100>=21/24)+(B1:B100<=6/24)
),C1:C1000)
 
Back
Top