Calculating all days but Friday

[Alismail]

Hi,

I would like some help in calculating vacation days used for our employees. Working days are Saturday thru Thrusday (Friday is off).

When we calculated vacation days we give each employee 27 working days. Meaning that Fridays will not be counted.

So what I need is to subtract the number of days each employee has requested by just seting the start day and the end day. This I have done.

The only problem is that I don't want to include any friday that might fall into the vacation period.

Example Employee takes vacation from Dec1 to Dec11, that means he will not work on either of those two dates and would be back on job Dec12. The days dedcuted, in this example should be 10 days only.

Thank you for your help.

[Lee Smith]

This subject has come up many times in the past.

Do an Advance search for [color:blue]+calculation +Workdays +only (Include the Pluses)), and you should see many examples, from; End Date - Start Date.... to a solution that includes holidays.

You can very the words used, and sometimes zero in on exactly what you are looking for.

HTH

Lee

Edited December 24, 2007 by Guest

[Søren Dyhr]

Try this tweak to Comment's calc from this thread:

http://www.fmforums.com/forum/showtopic.php?tid/155958/post/156042/#156042

...into this:

Let ( [

pattern = "1000011" ;



w = Div ( end - start + 1 ; 7 ) ;

r = Mod ( end - start + 1 ; 7 ) ;

p = Middle ( pattern&pattern ; DayOfWeek ( start ) ; r ) ;

x = w * PatternCount ( pattern ; "1" ) + PatternCount ( p ; "1" )

] ;

end - start + 1 - x

)

--sd

[Alismail]

Thanks, I will give it a try and let you know.

[Alismail]

Worked Perfectly.

You guys are the best. Thank You

[Fred in Thailand]

Soren,

I am using this in a solution and it also works great.

Is there way to get it to evaluate more than one pattern? Some of my employees work Saturdays and some don't.

Let ( [

pattern = "1100000" ;



w = Div ( Employee::EndDate-Employee::BeginDate+ 1 ; 7 ) ;

r = Mod ( Employee::EndDate-Employee::BeginDate+ 1 ; 7 ) ;

p = Middle ( pattern&pattern ; DayOfWeek (Employee::BeginDate) ; r ) ;

x = w * PatternCount ( pattern ; "1" ) + PatternCount ( p ; "1" )

] ;

(Employee::EndDate- Employee::BeginDate+ 1 - x)*28800

)

I need another Pattern in the same calc excluding Saturday

[Søren Dyhr]

It's a bit more tricky because, which of the weeks is it the saturdays should be excluded from, is the odd or the even numbered financial weeknumbers?

The pattern could easily be extended, but the offset from where to begin is ...requires defintion/business rule!

--sd

[comment]

Try something like:

Let ( [

pattern = Case ( WorksOnSaturdays ; "1000000" ; "1000001" ) ;

...

[Søren Dyhr]

Ah thats right! I misinterpreted or skipped this:

Some of my employees work Saturdays and some don't.

Another issue is that eventhough it isn't recursive, might there be a point in making it into a CF:

WorkDaysBetween(startDate;EndDate;Pattern)

--sd

[Fred in Thailand]

Lee,

Let ( [

pattern = Case ( [color:yellow]WorksOnSaturdays ; "1000000" ; "1000001" ) ;

Is This a new field being used as a flag?

[LaRetta]

Sooooooo, who you talkin' to Fred? Comment is Michael.

[Fred in Thailand]

Sorry,

Been a long week...

Let ( [

pattern = Case ( WorksOnSaturdays ; "1000000" ; "1000001" ) ;

Works great!!

Now just one more tweak...

Some work 6 hours on Saturday some work 8

Let ( [

pattern = Case ( Saturday="1" ; "1000000" ; "1000001" ) ;





w = Div ( Employee::EndDate-Employee::BeginDate+ 1 ; 7 ) ;

r = Mod ( Employee::EndDate-Employee::BeginDate+ 1 ; 7 ) ;

p = Middle ( pattern&pattern ; DayOfWeek (Employee::BeginDate) ; r ) ;

x = w * PatternCount ( pattern ; "1" ) + PatternCount ( p ; "1" )

] ;

(Employee::EndDate- Employee::BeginDate+ 1 - x)*28800

)

[LaRetta]

Now just one more tweak...

Some work 6 hours on Saturday some work 8

Good grief! How many times are you going to change the specs!? I hope you realize that, every time you change your requirements, your solution may possibly change COMPLETELY!? It seems that you're hitting a point where you should be using regular records to track employee times - you seem to have just too many variables.

I've been in this position ... adjusting and adjusting and adding duct tape here and there and sliding a side TO into a thing until it's so twisted and convoluted that it couldn't be figured out no matter WHAT one tried. It's the 'urban sprawl' syndrome and, each time there is a change, the entire solution should be re-evaluated. It feels like you might be at that point.

But then ... maybe Michael can save you.

UPDATE: Okay, I didn't see this thread consists of two different OPs. Ha ha ... but still ...

Edited December 26, 2007 by Guest
Added update

[comment]

LaRetta has a point: this calculation was designed to count days, not hours. When the number of hours per day is constant, it's easy to multiply the final result as you have done. But when it's not...

In a pinch, you could try something like:

Let ( [

pattern = "100000" & Saturday ;

...

where Saturday could be either "0" (doesn't work on Saturdays), "1" (works full-time on Saturdays) or "2" (works part-time on Saturdays) - but never empty.

Then you would need to adjust the rest of the calc to count "1" and "2" separately - both for the weeks and for the remainder.

[Fred in Thailand]

OK,

All your points are well taken.

The "Boss" Started by wanting just a simple way to track weather people were at work or not. It started as only an in out tracker. This guy changes his mind as often as I change my underwear. Fred, make it do this, Fred make it go that. I am very amazed i have gotten this far being new to database development. Attached is the solution. All comments are greatly appreciated.

Again thanks for all the help. You guys are the BEST

Happy Holidays

TimeLog.zip

Edited December 26, 2007 by Guest

[Søren Dyhr]

It's the 'urban sprawl' syndrome

Nice one! Which distinguished "knowledge workers" never suffer from

--sd

[Fred in Thailand]

Soren,

I hope that you are not suggesting that I am anywhere near the same as the person in the thread you referred to. I am very new to any kind of development work and use this forum as a way of learning what I need to get the job done.

In the short time I have been doing this, I have developed 7 solutions that all started as just simple ones and due to the nature of the boss I deal with, have progressed to the state of complete confusion. But.. they do the job that they need to do. I am very close to starting the first ones over with my newfound knowledge and am sure that I will do a much more logical and professional job.

I just want everyone to know that the help dispensed in this forum is worth much more than can ever be repaid.

Again, Thanks everyone. :yourock:

[Søren Dyhr]

No it's the other way round, GC sees herself on a divine mission to bash every tool she encounters, never giving in to there might be methods involved...

--sd