Return Value based on Day of Week

[nick.licausi]

I have the following situation:

I'm trying to key off of the agent ID in Column A and based on todays
day of the week return the start time of the agent from column D. I
can index it to get me the start time, but don't know how to formulate
the date part of the equation. Any help would be greatly appreciated!

Agent ID Agent Name Day Start Stop
--------- -------------------- --- ------ ------
2012069 Abel, Richard Howard Sun 18:00 0:00
Mon 18:00 0:00
Tue 18:00 0:00
Wed 18:00 0:00
Thu 18:00 0:00
Fri -OFF-- -OFF--
Sat -OFF-- -OFF--

 

[Joel]

weekday returns a number from 1 to 7 with Sunday equals 1. The row offset of
Sunday with respect to the agent row is 0, not 1 which is returned by weekday
function. therefore you must subtract 1 from weekday function to get the
offset of the row which contains the day of the week. See code below.

Sub gettimes()


daynumber = Weekday(Date)
agentnumber = 2012069

Set c = Columns("A:A").Find(what:=agentnumber, _
LookIn:=xlValues, lookat:=xlWhole)
If Not c Is Nothing Then
starttime = c.Offset(daynumber - 1, 3)
endtimetime = c.Offset(daynumber - 1, 4)

End If

End Sub