Does date fall within work schedule formula

[sthrncaliguy]

Hi,

I have a spreadsheet with rows of data. In each row of data, column A is a timestamp in the following format:

<TABLE style="WIDTH: 81pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=108 border=0><COLGROUP><COL style="WIDTH: 81pt; mso-width-source: userset; mso-width-alt: 3949" width=108><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl66 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 81pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" align=right width=108 height=17>11/30/2009 23:43</TD></TR></TBODY></TABLE>

What I need to do is have a formula compare whether or not the timestamp falls within an individuals set work schedule.

So say this is my data...

Date Employee Within Work Schedule?
11/30/2009 23:43 John Maydor
11/27/2009 18:46 Tim Jones


Work Schedules
John Maydor Mon. - Fri. 8:00-17:00
Tim Jones Tue. - Sat. 10:30 - 19:30

So in this example, I would want a formula that would return a "No" value for John Maydor's entry, and a "Yes" value for Tim Jones entry.

What would the formula look like in the "Within Work Schedule" column?

Also I can set the Work Schedule data table up however needed, I.E. I can put the start and end times in different cells if necessary.

 

[erik.van.geit]

Hi,

Seems like the Formula gurus are not yet answering, so let me put some sparetime in this one...

This solution will only work
1. if nobody starts working before sunday and stops after sunday.
2. if nobody starts working before midnight and stops after midnight

  A                B        C  D      E  F      G     H   I     J     
1 Date             Employee    result           day       hour        
2 30/11/2009 23:43 John     1  no        NAMES  start end start end   
3 27/11/2009 18:46 Tim      5  yes       John   2     6   08:00 17:00 
4 03/12/2009 12:25 other2   3  yes       other1 1     3   10:30 19:30 
5                                        other2 3     6   08:00 16:00 
6                                        other3 3     6   09:00 17:30 
7                                        Tim    3     7   10:30 19:30 
Sheet1
[Table-It] version 09 by Erik Van Geit

RANGE FORMULA (1st cell)
C2:C4 =MATCH(B2,$F$3:$F$7,0)
D2:D4 =IF(AND(WEEKDAY(A2)>=INDEX($G$3:$G$7,C2),WEEKDAY(A2)< =INDEX($H$3:$H$7,C2),MOD(A2,1)>=INDEX($I$3:$I$7,C2),MOD(A2,1)< =INDEX($J$3:$J$7,C2)),"yes","no")
[Table-It] version 09 by Erik Van Geit

-column C can be hidden
-start and end days can be formatted to display the name of the day: sunday = 1, monday = 2, ...
-this is all on the same sheet; you can put the right table on another sheet

kind regards,
Erik