Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Compare dates using an incremented date formula Post Reply Post New Topic
Author Message
HiPower
Newbie
Newbie
Avatar

Joined: 25 Jan 2012
Location: United States
Online Status: Offline
Posts: 3
Quote HiPower Replybullet Topic: Compare dates using an incremented date formula
     Posted: 25 Jan 2012 at 3:47am
Hello all,
 
My HR department has asked me for a report that calculates an employee's time for the day and if it is short and unexcused, points are assessed.  Too many points in a period and disciplinary action is initiated.
 
My problem is, we have every other Friday off.  If an employee chooses to work on that Friday, it is considered overtime.  At this point, these overtime Fridays falling within my formulas and assessing points because their time is short.
 
Is there a way to compare a date in the table to a incremented date formula and if true, not assess points?
 
Any help would be greatly appreciated.
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 475
Quote kostya1122 Replybullet Posted: 25 Jan 2012 at 6:44am
i only see a manual solution here
in your selection expert exclude every other Friday
table.date <> [date(2010,1,1), date(2010,1,8).....]
or if you have some sort of indicator in your data
that tells you which Friday is off
something like (fridays to exclude)
if dayofweek(table.date) = 6 and
sum(table.hours, table.date) < 60 (or something) then table.date

then you could putt
table.date <> (fridays to exclud)
into your selection expert.
IP IP Logged
HiPower
Newbie
Newbie
Avatar

Joined: 25 Jan 2012
Location: United States
Online Status: Offline
Posts: 3
Quote HiPower Replybullet Posted: 25 Jan 2012 at 7:08am
Ouch.  I don't have any indicators as to which Fridays are off.  I was hoping I could do something where I start with a past Friday off, increment that date by 7 days, take the new date and then increment that by 7 days, etc., potentially infinitely.  Once I got that, then I could compare the dates in our time and attendance table, compare it to the results and have it ignore a date that fits the criteria.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 25 Jan 2012 at 7:13am
I think you can use a formula to find your "every other friday" and use a formula to include or exclude based off of that.
 
here is an example using the first Friday of 2011 as your starting point
 
 
datediff('d',date(2011,1,7),{table.date}) mod 14
 
every other friday would return a 0 or a 7.
Hopefully this gives you something to work with.
 
 
IP IP Logged
HiPower
Newbie
Newbie
Avatar

Joined: 25 Jan 2012
Location: United States
Online Status: Offline
Posts: 3
Quote HiPower Replybullet Posted: 27 Jan 2012 at 4:37am
DBlank,
That worked great.  Thanks!  It does perform slightly differently than you described, though.  Instead of returning 0 or 7, it counts all the days between the Fridays off, then resets to 0 on the next Friday off.  Still, very usable formula.  I really appreciate the help.
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.