| Author |
Message |
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
  
Topic: Compare Month and Day to another set of dates
Posted: 22 May 2012 at 5:43am |
Hi, I'm in Crystal 2008. I have 3 date fields. {TAEEMASTER.MASTR_ENTRY}, {fbegPayPeriod} and {?Enter Pay Period End Date}, all formatted like mm/dd/yyyy. I need to be able to identify the employees where the month and day of the TAEEMASTER.MASTER_ENTRY data field fall between the month and day of fbegPayPeriod and ?Enter Pay Period End Date (These two fields are basically the beginning and end of a pay period).
If the employee's month and day of their MASTER_ENTRY date falls within this range, the employee qualifies for a contract salary increase.
Here's an example: Employees MASTER_ENTRY Date is 05/24/2001. The fbegPayPeriod date is 05/14/2012 and ?Enter Pay Period End Date is 05/27/2012. This employee qualifies for a salary increase, provided he or she is not at the top of their salary table (This is another formula I will need to figure out later).
Any suggestions would be greatly appreciated. Thanks!
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2012 at 7:17am |
you can use datepart('y',field), something like this maybe:
datepart('y',TAEEMASTER.MASTER_ENTRY) in datepart('y',fbegPayPeriod) to datepart('y',?Enter Pay Period End Date)
Note: this assumes you do not roll over a year in your range...eg Dec to Jan Edited by DBlank - 22 May 2012 at 7:52am
|
| IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 22 May 2012 at 7:31am |
Thanks for your input, I actually devised a formula that seems to be working. It also works when crossing over years:
If month({TAEEMASTER.MASTR_ENTRY})>=month( {@FBegPayPeriod}) then "Meets" else if month({TAEEMASTER.MASTR_ENTRY})<=month({?Enter Pay Period End Date}) then "Meets" else If day({TAEEMASTER.MASTR_ENTRY})>=day( {@FBegPayPeriod}) then "Meets" else if day({TAEEMASTER.MASTR_ENTRY})<=day({?Enter Pay Period End Date}) then "Meets" else "Does Not Meet"
Do you foresee any issue with this formula?
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2012 at 7:43am |
I do think you will run into problems but I do not know what you @FBegPayPeriod formula nor if you are looking at historical data or possible future data.
|
| IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 22 May 2012 at 7:52am |
The @fBegPay Period is a formula that looks at the pay period end date and subtracts 13 days to identify the first day of the pay period.
Actually....thanks for mentioning that. I forgot I will need look ahead to see who is going to hit the dates in the new pay period so we are not applying increases retro-actively. Thanks for that comment. Saved me a little extra work!
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2012 at 7:55am |
I assume master_entry is the hire date.
if you have a payperiod of Feb1,2012 to Mar 15 ,20212 and the hire date was April 1 2010 your formula is goint to show 'meets' even though the hire date is not in Feb or early April.
|
| IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 22 May 2012 at 8:22am |
Master Entry Date is actually the date the employee entered the Bargaining Unit (or Union). But now I see how this won't work. I needed to fix my formula so that I am comparing the Master Entry month and day with the NEXT pay period month and day. So, the new example of an employee that qualifies for the new salary increase is:
Master Entry Date = 01/01/2001. Next Pay Period Date ranges are 12/26/2011 to 01/09/2012.n Employee qualifies because month and day of Master Entry Date are between month and day of next pay period date range.
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 22 May 2012 at 10:48am |
probably a more elegant solution exists, but maybe this?
or
date(year({?Enter Pay Period End Date}),month({TAEEMASTER.MASTR_ENTRY}),day({TAEEMASTER.MASTR_ENTRY})) in {@FBegPayPeriod} to {?Enter Pay Period End Date} |
| IP Logged |
|
thummel1
Senior Member
Joined: 27 Apr 2012
Location: United States
Online Status: Offline
Posts: 140
|
Posted: 24 May 2012 at 3:06am |
I used both of these formulas and they were not successful. I am feeling a bit stuck with this
|
|
"Press any key to continue. Where's the 'Any' Key?" ~Homer Simpson
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 24 May 2012 at 3:49am |
they are intended to be used as one formula that has 2 conditions to handle if the begin to end goes over a year.
it should return a true or false
|
| IP Logged |
|
|
|
