Date/Time Diff excluding weekends

[szed]

I've seen and tested several of the other postings listed but none of them seem to address what I am trying to do.
My problem is that I have 2 dates, start and end date. I need to calculate the times between the two in Days, hours, minutes,and seconds however I need to exclude weekends. The start and end dates will never fall on a saturday or sunday, but can span over the weekend.

Most of the code I've seen is just testing for start and end days falling on weekends.

How can I get the DD:HH:MM if the record spans over the weekend? I am not using business hours so I don't have to worry about setting that anywhere, just 24 hour military time.

One example that I have is the start datetime is 2/3/06 2:24:49 pm and the end date is 2/7/06 7:34:00 am. I need to calculate the rest of the day on 2/3, count the 24 hours for 2/6/06, then the 7 1/2 hours for 2/7 but exclude the 48 hours for saturday and sunday, then convert that into Days:Hours:Minutes:Seconds.

Has anyone done this before??

 

[synapsevampire]

Yeah, that's a slightly different requirement from the usual.

And I guess you don't care about holidays.

Check out Ken Hamady's solution here:

http://www.kenhamady.com/form13.html

The only differences between his solution and your requirements are that you don't care about holidays, and you want all 24 hours for all dates except the first and last if they are weekdays.

Should be fairly easy to alter it accordingly.

-k

 

[szed]

thanks for the info, I will give it a try!

 

[PolkManiac]

I'm hoping someone can help me here as well...

I am trying to use the formula below, an altered formula from ken:

//Main formula
WhileReadingRecords;
Local DateVar Start = {V_BAR_SR.TAT_START_TIME};   // place your Starting Date here
Local DateVar End = {V_BAR_ACTIVITY.ACTUAL_END_DT};  // place your Ending Date here
Local NumberVar Weeks; 
Local NumberVar Days; 
Local Numbervar Hol;
DateVar Array Holidays;

Weeks:= (Truncate (End - dayofWeek(End) + 1 
- (Start - dayofWeek(Start) + 1)) /7 ) * 5;
Days := DayOfWeek(End) - DayOfWeek(Start) + 1 + 
(if DayOfWeek(Start) = 1 then -1 else 0)  + 
(if DayOfWeek(End) = 7 then -1 else 0);   

Local NumberVar i;
For i := 1 to Count (Holidays)
do (if Holidays[i] in start to end then Hol:=Hol+1 );

Weeks + Days - Hol[/code}

I'm placing the Holiday formula in the header of my report, and this formula in the details section.  The problem is I'm getting back all values of 1.00, when I can see that I should be getting back larger values.  

Am I missing something, any help is greatly appreciated...

 

[lbass]

Are you sure you are setting your start variable to a date and not a time?

-LB

 

[PolkManiac]

both variables are a date and time - data looks like this:

01/01/2006 12:36 am

 

[PolkManiac]

Also, I can create a formula that's just basically the end date - the start date and it works fine - it's the weekend piece that's throwing me, if that makes sense...so I basically know the fields are OK - it's something with this particular formula that isn't working for me...

 

[lbass]

You are missing the colons in these lines:

WhileReadingRecords;
Local DateVar Start [Red]:[/Red]= {V_BAR_SR.TAT_START_TIME};   // place your Starting Date here
Local DateVar End [Red]:[/Red]= {V_BAR_ACTIVITY.ACTUAL_END_DT};

-LB

 

[PolkManiac]

Cool, I got it working

Thanks for all your help