Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Day of Week & Time Problem 1

Status
Not open for further replies.

ecugrad

MIS
Apr 17, 2001
191
US
I appreciate any help, you experts are great...

I"m building a monthly report that will list the amount of accounts an individual has worked each day. The fields I am using are AccountNum, StartTime(DateTime) and EndTime(datetime) I'm able to pull data by the correct day of the month looking at the day part of the datetime field. and writing a formula like the one below for each day 1-31.
-------------------------------------------------------
If Day({PROCESSLOG.ENDTIME})=1
then 1
-------------------------------------------------------
My problem is that on Monday-Thursday we only want to count accounts that were worked between 8:15 PROCESSLOG.STARTTIME & 5:30 PROCESSLOG.ENDTIME. And on Friday 8:15 PROCESSLOG.STARTTIME & 6:00 PROCESSLOG.ENDTIME.
I put together a formula below, but I can't get it to work. I'm using CR8..

If Day({PROCESSLOG.ENDTIME})=1 and DayOfWeek({PROCESSLOG.ENDTIME}) in [1,2,3,4] and
Time({PROCESSLOG.STARTTIME})>=08:15:00 and Time({PROCESSLOG.ENDTIME})<=17:30:00
then 1


If Day({PROCESSLOG.ENDTIME})=1 and DayOfWeek({PROCESSLOG.ENDTIME}) in [5] and
Time({PROCESSLOG.STARTTIME})>=08:15:00 and Time({PROCESSLOG.ENDTIME})<=18:00:00
then 1
Thanks
 


If Day({PROCESSLOG.ENDTIME})=1 ...

This means {PROCESSLOG.ENDTIME} always has to be the 1st day of the month if the IF statement has any chance of evaluating to 1. Is this what you intended?
 
Yes, I know I went the long way to do this, but I wrote a formula like the one below for each day of the month, I just changed the day part to 1,2,3, 21, etc.. But I'm confused how to look at the day and see if it is a Monday-Thusday and if it is just pull accounts from 8:15 to 5:30. If it is a Friday just pull accounts from 8:15 to 6:00.

If Day({PROCESSLOG.ENDTIME})=1
then 1
else 0





We have an Incentive program that is based on how many accounts that an employee works. But if they work accounts after the above hours they get paid overtime and is not included in the program..

Thanks for looking at this for me..

 
The DayOfWeek function will default Sunday as the 1st day of the week. You can change this by including a second parameter in the function...

DayOfWeek({PROCESSLOG.ENDTIME}, crMonday) in [1,2,3,4]
 


If Day({PROCESSLOG.ENDTIME})=1 and DayOfWeek({PROCESSLOG.ENDTIME}) in [1,2,3,4] and
Time({PROCESSLOG.STARTTIME})>=time(8,15,00) and Time({PROCESSLOG.ENDTIME})<=Time(17,30,00)
then 1

else

If Day({PROCESSLOG.ENDTIME})=1 and DayOfWeek({PROCESSLOG.ENDTIME}) in [5] and
Time({PROCESSLOG.STARTTIME})>=time(08,15,00) and Time({PROCESSLOG.STARTTIME})<=time(18,00,00)
then 1
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top