wow, complicated

[mindfullsilence]

Hello everyone, new member here, how are all of you?

I need excel to look for a number in several ranges and return the number of occurrences that number is found. It's for a work schedule.

Example:
................Wednesday..............
cell..|.......A.........|.......B.......|
1.....|...Time in:....|...7:00 am..|
2.....|...Tim Out:...|..10:00 am.|
3.....|...Time in:....|...7:00 am..|
4.....|...Tim Out:...|..10:00 am.|
5.....|...Time in:....|..10:00 am.|
6.....|...Tim Out:...|...3:00 pm..|

I would need excel to test the ranges for "Time In" and "Time Out" and see if, say, 9:00 AM fell inside the range, and because it does, it would give me a "2." one for B1:B2, and another for B3:B4. After testing B5:B6 it wouldn't add anything because 9:00 AM does not fall between 10:00 AM and 3:00 PM.

I need it to test about 20 of these ranges and tell me how many times "9:00 am" exists.

I have to have this formula for every hour of the day from 7am - 2am. And for each day of the week, so I'm hoping it's simple.

Any ideas?

btw, I have no idea about macros or [COLOR=blue ! important][FONT=Verdana,Helvetica,sans-serif][COLOR=blue ! important][FONT=Verdana,Helvetica,sans-serif]vba[/FONT][/FONT][/color][/color] so I'm hoping this is possible with a formula. I'm definitely not an expert but I'm a little above average on formulas I think.

I'm trying to be as clear as possible without implying that I have little confidence all of you, but I've posted on a couple other forums to no avail. Perhaps I'll strike gold here. If it would help I can post a link to download my file I need this for.

Thanks in advance.

 

[Makrini]

Hi and welcome to the best place for help - MrExcel Forums!

use this formula...

=SUMPRODUCT((A1:A1000="Time In")*(B1:B1000<E1)*(OFFSET(B1:B1000,1,0)>E1))

anywhere you want - then put the time you want to check - into E1

Change E1 as much as you like...

 

[arthurbr]

Hi and welcome to the board,
you can use info on this page http://www.cpearson.com/excel/DateIntervals.htm
to check if a given time falls in a given interval.
Adapt the formula to time instead if dates

Cheers

 

[iliace]

This may not be the most elegant solution, but it should give you what you're looking for. Let's say your range is in B2:B29, where B2 is the first Time in, and B29 is the last Tim Out. H1 is your input time (the hour you want to count in range).

=SUMPRODUCT(--($B$2:$B$28<=H1),--($B$3:$B$29>=H1),--ISODD(ROW($B$3:$B$29)),--ISEVEN(ROW($B$2:$B$28)))

ISODD should encapsulate whichever range begins and ends on the odd row; ISEVEN should be for the even rows. Hope that helps.

 

[Makrini]

oops the board cut off half of my formula -

use iliace's anyway - same principle

 

[iliace]

oops the board cut off half of my formula -

use iliace's anyway - same principle

I was wondering! I thought, that can't possibly work, unless he knows some syntax that I don't... but I'm too sleepy to try it, late-night Excelling here.

 

[mindfullsilence]

iliace, that formula, after adjusting cell numbers to fit my file, gave me a #name? error. any ideas?

 

[Makrini]

=SUMPRODUCT((A1:A1000="Time In") * (B1:B1000 < E1) * (OFFSET(B1:B1000,1,0) > E1))

is what I originally meant to send (it didn't like the " > " I had without spaces)

 

[mindfullsilence]

iliace, that formula, after adjusting cell numbers to fit my file, gave me a #name? error.

I have it setup like this now...

"=SUMPRODUCT(--($E$5:$E$35<=P24),--($E$6:$E$36>=P24),--ISODD(ROW($E$5:$E$35)),--ISEVEN(ROW($E$6:$E$36)))"

Where E5 is the First Time In and E35 is the last Time In, E6 is the first Time Out and E36 is the last Time Out. P24 is where I have the time I'm looking for.

any ideas?

 

[mindfullsilence]

here's a link to download my spreadsheet

http://www.excelforum.com/attachment.php?attachmentid=21366&d=1208836437