Resouce work time Calculation

[Tissot]

This Calc is driving me insane. Look forward to some pointers.

I need to calculate the hours between two dates but only the working hours. Also the weekends and holidays would have to be deducted.

I've got StartWorkTime and EndWorkTime per resouce also got a List of all theyre holidays. Now I need to combine both and get all this to give me the hours in return.

So say a Job starts 18.8.08 at 13:00 (timestamp) and ends at 26.8.08 17:00 and the the Start time is 7:00 and end is 15:00 = 10:00 h / day. Now the guy has 19.8.08 and 20.8.08 vacation. This should result with:

18.8.08 = 4h, 19.8.08 = 0h, 20.8.08 = 0h, 21.8.08 = 10h, 22.8.08 = 10h, 23.8.08 = 0h, 24.8.08 = 0h 25.8.08 = 10h, 26.8.08 = 8h

Total 42h

ResourceHours(JobStartTimestamp;JobEndTimestamp;WorkStartTime;WorkEndTime;HolidayList)

ResourceHours(18.8.08 13:00;26.8.08 17:00;7:00;17:00;19.8.08¶20.8.08) would result with 42

I realize there needs to be some Cf but would be glad for some advice.

Cheers

Edited August 15, 2008 by Guest

[comment]

You could start with:

http://www.fmforums.com/forum/showtopic.php?tid/190174/

[Tissot]

Hello Michael

I've managed to put something together but its not working yet. Can you give me a pointer?

Heres the code:

Let([

StartD = FilterValues( Date(JobStartTimestamp; JobStartTimestamp; JobStartTimestamp) ;HolidayList);

EndD = FilterValues( Date(JobEndTimestamp; JobEndTimestamp; JobEndTimestamp) ;HolidayList);

EndTS = JobEndTimestamp;

StartT = If(Time(JobStartTimestamp; JobStartTimestamp; JobStartTimestamp) = Time(0;0;0); WorkStartTime; Time(JobStartTimestamp; JobStartTimestamp; JobStartTimestamp));

EndT = If(Time(JobEndTimestamp; JobEndTimestamp; JobEndTimestamp) = Time(0;0;0); WorkEndTime; Time(JobEndTimestamp; JobEndTimestamp; JobEndTimestamp));

WorkSTime = WorkStartTime;

WorkETime = WorkEndTime;

ZiroT = Time(0;0;0)

];

Case(

StartT > WorkSTime; TimeAsDecimal(StartT) - TimeAsDecimal(WorkSTime);

StartT = WorkSTime and EndT < WorkETime; TimeAsDecimal(WorkETime) - TimeAsDecimal(WorkSTime);

StartT = WorkSTime and EndT = WorkETime; TimeAsDecimal(WorkETime) -TimeAsDecimal(WorkSTime);

EndT < WorkETime; TimeAsDecimal(WorkETime) -TimeAsDecimal(EndT)

) 

& If(EndD ≤ Date(EndTS;EndTS;EndTS); +  ResourceHours(Timestamp(StartD + 1 ; ZiroT) ;Timestamp(EndD ; If( Date(EndTS; EndTS; EndTS) = (StartD + 1);EndT;ZiroT));WorkSTime;WorkETime;HolidayList)))

[comment]

Frankly I have no idea what you're doing.

[Tissot]

I've rewrote the CF with comments, hope its more clear now. It brings up a questionmark when I use it.

ResourceHours(JobStartTimestamp;JobEndTimestamp;WorkStartTime;WorkEndTime;HolidayList)

Let([

StartTS = JobStartTimestamp;

EndTS = JobEndTimestamp;

StartD = Date(Month(JobStartTimestamp); Day(JobStartTimestamp); Year(JobStartTimestamp));

EndD = Date(Month(JobEndTimestamp); Day(JobEndTimestamp); Year(JobEndTimestamp));

StartT = Time(Hour(JobStartTimestamp); Minute(JobStartTimestamp); Seconds(JobStartTimestamp));

EndT = Time(Hour(JobEndTimestamp); Minute(JobEndTimestamp); Seconds(JobEndTimestamp));

LastJobDay = If(StartD = EndD;1;0);

WorkSTime = WorkStartTime;

WorkETime = WorkEndTime

];



/* We put the whole calc in another "Let" so we can create a number instead of time. See at the end. */



Let([

calc = 



/* No reverse allowed! StartDate has to be bigger than EndDate */

If(StartTS > EndTS or WorkSTime > WorkETime;0;



/* Core Calc */

Case(

/* Filter Weekends */

DayOfWeek (StartD) = 1 or DayOfWeek (StartD) = 7 ; 0;



/* Filter Holidays */

FilterValues(StartD;HolidayList)>0 ; 0;



/* If JobStartTime is later than WorkStartTime and its the last JobDay then Calculate JobEndTime - JobStartTime */

StartT > WorkSTime and LastJobDay = 1 ; If(EndT > WorkETime;WorkETime;EndT) - StartT;



/* If JobStartTime is later than WorkStartTime and its NOT the last JobDay then Calculate JobStartTime - WorkEndTime */

StartT > WorkSTime and LastJobDay = 0 ; WorkETime - If(StartT > WorkETime;WorkETime;StartT);



/* If JobStartTime is equal or earlier than WorkStartTime and its the last JobDay then Calculate JobEndTime - WorkStartTime */

StartT ≤ WorkSTime and LastJobDay = 1 ; If(EndT > WorkSTime;If(EndT > WorkETime;WorkETime;EndT - If(StartT < WorkSTime;WorkSTime;StartT)));



/* If JobStartTime is equal or earlier than WorkStartTime and its NOT the last JobDay then Calculate WorkEndTime - WorkStartTime */

StartT ≤ WorkSTime and LastJobDay = 0 ; WorkETime - WorkSTime;

0)

)



];



/* Change the whole Calculation to Decimal Time, so 1:00:00 becomes 1 in a number */

Round(Hour(calc) + (((Minute(calc) * 100) / 60) / 100); 2)

)



/* Add the Hours recursive */

+



/* Then run this until LastJobDay = 1 */

Case(LastJobDay = 0;ResourceHours(Timestamp(StartD + 1;WorkSTime);EndTS;WorkSTime;WorkETime;HolidayList))

)

Thanks.

Edited August 15, 2008 by Guest

[comment]

Three suggestions:

1. I would calculate the hours separately and let the custom function deal with dates only.

2. When something is not working, break it up into smaller parts and check the result of each part.

3. Perhaps you need to get more familiar with functions and calculations before attempting something like this. I stopped reading at the third line of your formula:

StartD = Date(JobStartTimestamp; JobStartTimestamp; JobStartTimestamp);

That makes absolutely no sense.

[Tissot]

Thanks for your help, Michael!

Actually the calc was pretty much bang on after that small typo you pointed out.

I've finaly finished this super CF!

Let([

StartTS = JobStartTimestamp;

EndTS = JobEndTimestamp;

StartD = Date(Month(JobStartTimestamp); Day(JobStartTimestamp); Year(JobStartTimestamp));

EndD = Date(Month(JobEndTimestamp); Day(JobEndTimestamp); Year(JobEndTimestamp));

StartT = Time(Hour(JobStartTimestamp); Minute(JobStartTimestamp); Seconds(JobStartTimestamp));

EndT = Time(Hour(JobEndTimestamp); Minute(JobEndTimestamp); Seconds(JobEndTimestamp));

LastJobDay = If(StartD = EndD;1;0);

WorkSTime = WorkStartTime;

WorkETime = WorkEndTime

];



/* We put the whole calc in another "Let" so we can create a number instead of time. See at the end. */



Let([

calc = 



/* No reverse allowed! StartDate has to be bigger than EndDate */

If(StartTS > EndTS or WorkSTime > WorkETime;0;



/* Core Calc */

Case(

/* Filter Weekends */

DayOfWeek (StartD) = 1 or DayOfWeek (StartD) = 7 ; 0;



/* Filter Holidays */

FilterValues(StartD;HolidayList)>0 ; 0;



/* If JobStartTime is later than WorkStartTime and its the last JobDay then Calculate JobEndTime - JobStartTime */

StartT > WorkSTime and LastJobDay = 1 ; If(EndT > WorkETime;WorkETime;EndT) - StartT;



/* If JobStartTime is later than WorkStartTime and its NOT the last JobDay then Calculate JobStartTime - WorkEndTime */

StartT > WorkSTime and LastJobDay = 0 ; WorkETime - If(StartT > WorkETime;WorkETime;StartT);



/* If JobStartTime is equal or earlier than WorkStartTime and its the last JobDay then Calculate JobEndTime - WorkStartTime */

StartT ≤ WorkSTime and LastJobDay = 1 ; If(EndT > WorkSTime;If(EndT > WorkETime;WorkETime;EndT - If(StartT < WorkSTime;WorkSTime;StartT)));



/* If JobStartTime is equal or earlier than WorkStartTime and its NOT the last JobDay then Calculate WorkEndTime - WorkStartTime */

StartT ≤ WorkSTime and LastJobDay = 0 ; WorkETime - WorkSTime;

0)

)



];



/* Change the whole Calculation to Decimal Time, so 1:00:00 becomes 1 in a number */

Round(Hour(calc) + (((Minute(calc) * 100) / 60) / 100); 2)

)



/* Add the Hours recursive */

+



/* Then run this until LastJobDay = 1 */

Case(LastJobDay = 0;ResourceHours(Timestamp(StartD + 1;WorkSTime);EndTS;WorkSTime;WorkETime;HolidayList))

)

It does error checking an all of it...

Cheers

Edited August 16, 2008 by Guest

[Tissot]

Fot the complete Custom Function with decription and sample visit:

Custom Function for calculating resource hours

Maybe saves someone else breaking they’re head over it.

[Lee Smith]

Shouldn't it return 24, rather than 36?

How many hours a day are they working?

I make it to be 6 hours.

How many work days?

I make it to be 4, including the 13th, and 20th.

Another problem exist with the Holidays. The CF returned 36 before I entered the two holidays, and it didn't change after I entered them.

I noticed that you use the old way of parsing the date and time, check out the GetAsDate, and GetAsTime Functions.

HTH

Lee

[Tissot]

No, because the 13th, 18th, 19th and 20th * 10 (17:00 - 7:00 (Work time) = 10) and that * 4 days = 40, but since they start at 9:00 the first day and stop at 15:00 the last day, we need to subtract 4h making it "36".

Good point about the Holidays, it only works with a full date e.g. "01.01.2008" not "1.1.08" because of filtering. You would need another CF to generate the Proper Dates.

Something like this:

ProperDate ( DateList )

Let ( [

count = ValueCount ( DateList );

lv = GetAsDate ( RightValues ( DateList ; 1 ) ) ;

rv = LeftValues ( DateList ; count - 1 )

];

If( count = 1 ; "" ; ProperDate ( rv ) & "¶" ) & lv

)

This should create proper dates for you and then you can pass it in as the Holiday list.

Thanks for the Tip with the GetAsDate and GetAsTime, I've already changed it.

Give it another try with the new Code from Brians site and let me know. Thanks for pointing.

Ron

Edited August 19, 2008 by Guest