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 Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Loop problems calculating working hours

Status
Not open for further replies.

angrymeeow

Programmer
Oct 17, 2002
24
0
0
ZA
Ok here goes...

I'm trying to calculate the number of working days hours minutes etc. between two dates. I've written a formula that should give me this results in seconds (cause I need to do a little formatting some where else). My problem is my test data has roughly two days between them and when I run my report I get an error saying that the loop executed more then the allowed number of times. Can anyone see what I am doing wrong?

Code:
Data:
  StartDate = 2/11/2005 04:15:00 PM
  EndDate = 2/13/2005 06:44:42 PM
Code:
dateTimeVar start:= {Table.STARTDATE};
dateTimeVar end:= {Table.ENDDATE};

numbervar tsecs:= DateDiff("s",start, end); //The total difference between start and end

//strip out time and add start and end of working day
dateTimeVar startdate:= DateTime (Date(start), Time (07, 00, 00));
dateTimeVar enddate:= DateTime (Date(start), Time (19, 00, 00));

numbervar numsats:= DateDiff("ww", start - 1, end, 7); //Get number of Saturdays in range
numbervar numsuns:= DateDiff("ww", start - 1, end, 7); //Get number of Saturdays in range

//Remove any time before real start if any
if start < startdate then
    tsecs:= tsecs - DateDiff("s",start, startdate);

//Remove any time after real end if any
if end > enddate then
    tsecs:= tsecs - DateDiff("s", enddate, end);

//Remove 12hrs for each Saturday - these will be the 12hrs between 7:00 and 19:00 at the other 12hrs will be handled later
if numsats > 0 then
    tsecs:= tsecs - (numsats * 43200);

//Remove 12hrs for each Sunday - these will be the 12hrs between 7:00 and 19:00 at the other 12hrs will be handled later
if numsuns > 0 then
    tsecs:= tsecs - (numsuns * 43200);

startdate:= DateAdd("h", 12 , startdate); //Add 12hrs to startdate to make time end of working day

//Remove 12hrs for each day between start and end dates
while startdate < enddate do
    tsecs:= tsecs - 43200;
    startdate:= DateAdd("d", 1, startdate);    

tsecs

[red]G[/red][purple]r[/purple][blue]r[/blue][green]r[/green][yellow].[/yellow][white]..[/white][yellow]m[/yellow][green]e[/green][blue]e[/blue][purple]o[/purple][red]w[/red] [cat2]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top