Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Value based on Day of Week
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-- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Return Value based on Day of Week
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 " wrote: 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-- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Return dates based on month and day of week | Excel Worksheet Functions | |||
How to return week number from date? | Excel Worksheet Functions | |||
How to return week number from date? | Excel Worksheet Functions | |||
Return day of week for 1 January | Excel Worksheet Functions | |||
Return the day of week for 1 January | About this forum |