NEED HELP WITH EXCEL TIME BASED FORMULA

[IncredibleVolk]

I was hoping someone might be able to help with a formula I'm attempting. We classify different time segments throughout the day as prime hours and I need to total those hours. The biggest problem is that the prime hours for Saturday are different than the prime hours of Monday through Friday. I need to total the time someone has inside of the prime hours for that day.

TOTAL PRIME HOURS WORKED

COLUMN A: DATE
COLUMN F: START TIME
COLUMN J: FINISH TIME

MONDAY THROUGH FRIDAY PRIME HOURS ARE 11:00 A.M. TO 1:30 P.M. AND 4:30 P.M. TO 8:00 P.M.
SATURDAY PRIME HOURS ARE 8 A.M. TO 11:30 P.M.

I'VE GOT STARTED ON THE PART THAT DETERMINES IF IT'S MONDAY THROUGH FRIDAY BUT I FIGURED THE MULTIPLE NESTS I'D NEED TO CREATE MIGHT EXPLODE MY BRAIN

=IF(OR(AND(WEEKDAY(C5)>=2,WEEKDAY(C5)<=6),AND(F5<>"")), J5-F5,"")

 

[SkipVought]

Hi,

First set up a table using Named Ranges with NAMES in the TOP row.
[tt]
Seq From Thru
1 11:00 13:30
2 16:30 20:00
3 8:00 11:30
[/tt]
Relative to the row containing the Date, Start & Finish...
In the next adjacent column AND the next TWO (for a total of THREE columns..
TWO ROWS ABOVE
enter 1, 2 and 3, corresponding to the THREE Seq values in the lookup table.. (Mine are in K8:M8)
ONE ROW ABOVE
enter below the 1 & 2 (Mine are in K9:M9)
[tt]
=AND(WEEKDAY(Date)>=2,WEEKDAY(Date)<=6)
[/tt]
enter below the 3
[tt]
=WEEKDAY(Date)=7
[/tt]
enter in the row and copy across...
[tt]
=IF(OR(Start_Time>INDEX(Thru,K$8,1),Finish_Time<INDEX(From,K$8,1)),0,IF(Finish_Time<=INDEX(Thru,K$8,1),IF(Start_Time<INDEX(From,K$8,1),Finish_Time-INDEX(From,K$8,1),Finish_Time-Start_Time),IF(Start_Time>=INDEX(From,K$8,1),INDEX(Thru,K$8,1)-Start_Time,INDEX(Thru,K$8,1)-INDEX(From,K$8,1))))*K9
[/tt]
Now the PRINE TIME for that Date (row) is the SUM of your three formulas

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

I realized that I made a mistake

IGNORE the ONE Row Above. That data is ROW specific.

Rather, change the formula to incorporate the weekday function.

This formula for columns 1, & 2
[tt]
=IF(OR(Start_Time>INDEX(Thru,K$8,1),Finish_Time<INDEX(From,K$8,1)),0,IF(Finish_Time<=INDEX(Thru,K$8,1),IF(Start_Time<INDEX(From,K$8,1),Finish_Time-INDEX(From,K$8,1),Finish_Time-Start_Time),IF(Start_Time>=INDEX(From,K$8,1),INDEX(Thru,K$8,1)-Start_Time,INDEX(Thru,K$8,1)-INDEX(From,K$8,1))))*IF(AND(WEEKDAY(Date)>=2,WEEKDAY(Date)<=6),1,0)
[/tt]
This formula for column 3
[tt]
=IF(OR(Start_Time>INDEX(Thru,M$8,1),Finish_Time<INDEX(From,M$8,1)),0,IF(Finish_Time<=INDEX(Thru,M$8,1),IF(Start_Time<INDEX(From,M$8,1),Finish_Time-INDEX(From,M$8,1),Finish_Time-Start_Time),IF(Start_Time>=INDEX(From,M$8,1),INDEX(Thru,M$8,1)-Start_Time,INDEX(Thru,M$8,1)-INDEX(From,M$8,1))))*IF(WEEKDAY(Date)=7,1,0)
[/tt]
My attempts at a generic formula failed: Hence TWO.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[IncredibleVolk]

Skip,

Is there any chance you can forward a spreadsheet example to jamesvolkman@yahoo.com ? I understand the corresponding sequence values but it's hard to follow everything. I appreciate your help as I could have never considered this approach.