Counting hours in any given time frame

[Lhuffst]

I have a formula that counts the days worked for a specified timeframe. It runs fine until someone runs a report that spans 2 different years (in this case Dec 2011 to Jan 2012). The code I have is this....can anyone see how I would change month 12 to count correctly if the ending date is in a new year?

If Month({Command.MYDATE}) =  01  Then
  If Month({Command.MYDATE}) = Month({?BeginDate}) and 
          Month({Command.MYDATE}) = Month({?EndDate}) then
          (
           Start := {?BeginDate};
           End := {?EndDate}
          )
    else
	(
	   Start := Date(Year({?BeginDate}),01,01);
	   End := Date(Year({?EndDate}),01,31)
	)
Else If Month({Command.MYDATE}) =  02   Then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        )
    else
	(
   	Start := Date(Year({?BeginDate}),02,01);
   	End := Date(Year({?EndDate}),02,28)
	)
Else If  Month({Command.MYDATE})  =  03  Then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        )
    else
	(
	   Start := Date(Year({?BeginDate}),03,01);
	   End := Date(Year({?EndDate}),03,31)
	)
Else If  Month({Command.MYDATE})  =  04  Then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        )
    else
	(
	   Start := Date(Year({?BeginDate}),04,01);
	   End := Date(Year({?EndDate}),04,30)
	)
Else If  Month({Command.MYDATE}) =  05  Then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        )
    else
	    (
	    Start := Date(Year({?BeginDate}),05,01);
	    End := Date(Year({?EndDate}),05,31)
	    )
Else If Month({Command.MYDATE})  =  06   Then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        
        )
    else
	    (
	    Start := Date(Year({?BeginDate}),06,01);
	    End := Date(Year({?EndDate}),06,30)
	    )
Else If Month({Command.MYDATE})  =  07 then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        )
    else
        (
         Start := Date(Year({?BeginDate}),07,01);
        End := Date(Year({?EndDate}),07,31)
        )
Else If  Month({Command.MYDATE})  =  08   Then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        )
    else
	    (
	   Start := Date(Year({?BeginDate}),08,01);
	   End := Date(Year({?EndDate}),08,31)
    	)
Else If  Month({Command.MYDATE})  =  09  Then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        )
    else
	(
	   Start := Date(Year({?BeginDate}),09,01);
	   End := Date(Year({?EndDate}),09,30)
	)
Else If  Month({Command.MYDATE})   =  10  Then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        )
    else
	(
	   Start := Date(Year({?BeginDate}),10,01);
	   End := Date(Year({?EndDate}),10,31)
	)
Else If  Month({Command.MYDATE})  =  11  Then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        )
    else
	(
	   Start := Date(Year({?BeginDate}),11,01);
	   End := Date(Year({?EndDate}),11,30)
	)
Else If  Month({Command.MYDATE}) =  12   Then
    If Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = Month({?EndDate}) then
        (
         Start := {?BeginDate};
         End := {?EndDate}
        )
    else
    if Month({Command.MYDATE}) = Month({?BeginDate}) and 
        Month({Command.MYDATE}) = 01  then
        (
	   Start := Date(Year({?BeginDate}),12,01);
	   End := Date(Year({?EndDate}),01,31);
	)
    else
	(
	   Start := Date(Year({?BeginDate}),12,01);
	   End := Date(Year({?EndDate}),12,31)
	);

or maybe a better way to do this portion? All I'm doing is passing the start and end dates to another formula. Thanks lhuffst

 

[SkipVought]

hi,

DURATION is simply calculated by END Date - START Date, and the units of this calculation is DAYS.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Lhuffst]

Skip I apologize for the confusion. The count that I'm trying to do is count the number of days someone worked in a given time frame minus weekends minus holidays minus sick leave etc. I used Ken Hamaday's formulas and that part work great. what happened is the user selected Dec 2011 and Jan 2012 for their timeframe (usually report is run as a calendar year or one month) so I got an 11 for the holidays because I did somthing wrong on my formula if the month is 12.

My thought was this:
If the start date month is 12 and the end date month is 12, then you are running it for 1 month so just pass both the start/end dates

If the start date month is 12 and the enddate month is something else (in this case 01) then I want to count ONLY the holidays in December and January which for us should have been 3.

I put in temporary fields to see how my formula was translating and for this timeframe it showed
Start Date 12/01/2011
End Date 12/31/2012
which is why I know my formula is wrong but I'm just not sure what to change.
Thanks for all your help. lhuffst

 

[RTag]

Lhuffst, you may achieve the same result in a SQL query. The report will be faster and the code will be smaller.

Check this sample:

http://r-tag.com/OvertimeCalculation.aspx

It is not what you are looking for , but shows how to calculate weekends and even overtime hours. Your query would be simpler than that.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[MCuthill]

Lhuffst,

Perhaps Ken Hamady can shed some light on your inquiry.
Link:

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

Cheers!

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."