How only calculate weekday hours in a formula 1

[VBugDenver]

I reveived the very valuable information below for LBass. It work perfectly. But after looking at the data, it calculates all hours in in a week. Now I am getting into an area that I am less familiar. My question; is there a way to eliminate weekend hours and only calculate the hours Monday thru Friday and display the data as day:hours? example: 3 days, 5 hours in any format.


lbass (TechnicalUser) 3 Mar 11 13:05
If you want to display the results in a group footer, you can use formulas like this:

//{@treatment}
if {Schedule.Activity} = "Treatment" then
{Schedule App_DtTm}

//{@simulation}:
if {Schedule.Activity} = "Simulation" then
{Schedule App_DtTm}

Make sure the case of the result matches what is in the field. Then create a third formula:

datediff("d",maximum({@treatment},{table.groupfield}),maximum({@simulation},{table.groupfield}))

Not sure what interval you want use "s" for seconds, "n" for minutes, "h" for hours.

This assumes there is only one treatment and one simulation per group.

-LB

 

[lbass]

Can you clarify whether for M-F you want to see 24 hours? Or do you also want weekday hours limited to some work schedule, e.g., 9:00 to 5:00?

-LB

 

[VBugDenver]

Hi lbass, Thanks for getting back to me. I ws out of the office on Friday, so sorry I did not get back to you.

I would like to calculate only weekday hours between 6:30 am and 7:00 pm (Monday thru Friday).

Thanks, John

 

[lbass]

Please see Ken Hamady's solution:

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

-LB

 

[VBugDenver]

Thanks lbass, I took your suggested link and developed the following formula. This formula is supposed to count the days bettwe the start date (1/19/2011) and end date (1/21/2011). The results of the formula should be 3 but I am getting a result of 1. I added a condition to the formula and I get no error on the formula in Cystal. I do hav the holiday formulas completed and incerted in the report header as instructed in the link. Does anyone have any ideas what is wrong with this formula below?


WhileReadingRecords;

If {Schedule.Activity} = "Simmulation" then
Local DateVar Start = date ({Schedule.App_DtTm});
If {Schedule.Activity} = "Volumes Approved" then
Local DateVar End = date ({Schedule.App_DtTm});
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 DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

 

[lbass]

If {Schedule.Activity} = "Simmulation" then
Local DateVar Start := date ({Schedule.App_DtTm});
If {Schedule.Activity} = "Volumes Approved" then
Local DateVar End := date ({Schedule.App_DtTm});

You forgot the colons in the above section.

-LB

 

[VBugDenver]

Thanks LB, I did add the colons and I get no error in the formula but it still does not return the right data. I have a record that have a simulation date of 1/19/2011 (Wed) and a Volumes Approved of 1/21/2011 (Fri). The result should be 3. It still returns a result of 1. I am wondering if it is looking at the {schedule.App_DtTm} for the simulation then the {schedule.App_DtTm} for the Volumes Approved and caluculating the days between the two? Below is the formula:

WhileReadingRecords;

If {Schedule.Activity} = "Simulation" then
Local DateVar Start := date ({Schedule.App_DtTm});
If {Schedule.Activity} = "Volumes Approved" then
Local DateVar End := date ({Schedule.App_DtTm});
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 DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

 

[VBugDenver]

I just tried putting the if statement within the local dateVar Start and End but I get the same result. as stated above.

WhileReadingRecords;

Local DateVar Start := If {Schedule.Activity} = "002881" then
date ({Schedule.App_DtTm});
Local DateVar End := If {Schedule.Activity} = "Volumes Ap" then
date ({Schedule.App_DtTm});
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 DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days - Hol

 

[VBugDenver]

I even tried putting the if statement in parentheses. The formula had no errors but I still get the same result.

WhileReadingRecords;

Local DateVar Start := (If {Schedule.Activity} = "002881" then
date ({Schedule.App_DtTm}));
Local DateVar End := (If {Schedule.Activity} = "Volumes Ap" then
date ({Schedule.App_DtTm}));
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 DayOfWeek ( Holidays ) in 2 to 6 and
Holidays in start to end then Hol:=Hol+1 );

Weeks + Days – Hol

 

[raja2008]

Hello !

I had the same request and here is what i did.
Please see if this helps you.

thread767-1531876

 

[MCuthill]

VBugDenver,

This formula is supposed to count the days bettwe the start date (1/19/2011) and end date (1/21/2011). The results of the formula should be 3 but I am getting a result of 1.

Forgive me if I am out in left field... but is there not only one day between 1/19/2011 & 1/21/2011 (namely January 20th)?

Perhaps something as simple as adding 2 to your result will resolve the isssue?

Hope this helps.

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[VBugDenver]

No, the following link states it will count both the start day and end day so the answer should be 3. I only get the result of 1 no matter howmany days are between, sonthing is not calulating right and I can't figure out where the problem in the formula is.

http://www.kenhamady.com/form01.shtml

states:

NOTE - this formula counts both the starting date AND the ending date, if both are work days. So if you start on Monday and end on Tuesday, this counts as 2 days.