9 Replies Latest reply on Aug 29, 2012 1:56 PM by dchabot

# Need to determine the 12th of the month

Hi,

I need to indicate whether or not an employee worked on the 12th day of each month during a reporting period.

The reporting period would be for each quarter.

This is the exact information needed:

12th of the month - month 1

Indicate whether or not the employee worked on the 12th day of each month during the reporting period.

Enter "1" if the employee worked during the week in the third month of the quarter that contained the 12th of

the month. Enter zero if the employee did not work during the period.

12th of the month - month 2 - same as above

12th of the month - month 3 - same as above

The pay date field may not necessarily be exactly the 12th day of the month.

For example:

1/6/2012

1/13/2012 = "1"

1/20/2012

1/27/2012

2/3/2012

2/10/2012

2/17/2012 = "1"

2/24/201

3/2/2012

3/9/2012

3/16/2012 - "1"

3/23/2012

3/30/2012

Let M1 = "week of the month that has the 12th in it"

Let M2 = "week of the month that has the 12th in it"

Let M3 = "week of the month that has the 12th in it"

It's the 3rd week (5 days) of each month range: Month(Week of Month=3 ???

What is the formula to give the week of the month that has the 12th in it?

This could then be:

Case(PayPeriod=M1;1

PayPeriod=M2;1

PayPeriod=M3;1;0)

Excuse any of my thought processes above, as I'm starting to ramble on.......

This is possible to do in FM isn't it?

Thanks so much

DChabot

• ###### 1. Re: Need to determine the 12th of the month

I got a bit confused, esp regarding what information is available to begin with. If you have a date field, and you want to find out if the date is in the same week* that contains the 12th of the month, try =

```WeekOfYear ( YourDate ) =  WeekOfYear ( Date ( Month (YourDate ) ; 12 ; Year ( YourDate ) ) )
```

---

(*) A "week" in this context means Sunday to Saturday. inclusive.

Message was edited by: Michael Horak

• ###### 2. Re: Need to determine the 12th of the month

Michael,

You nailed it!!!

This fills in the correct '1' for every week of payperiod that includes the 12th of month for a year.

Work perfect on the detail report.

Here's the frustrating part.

How do I get this detail over to a summary of this data for a quarter?

I need to show a '1' not a summary of all the one's.

• ###### 3. Re: Need to determine the 12th of the month

If this is for display only, you can format the summary field to display as Boolean.

• ###### 4. Re: Need to determine the 12th of the month

Great idea but it's being exported to Excel.

• ###### 5. Re: Need to determine the 12th of the month

Even as boolean it doesn't carry over into the Summary.

• ###### 6. Re: Need to determine the 12th of the month

dchabot wrote:

Great idea but it's being exported to Excel.

Then how about changing the summary field to show the maximum of the calculation field? If, for some reason, you do need the total, you will need to add a calculation field, e.g. =

GetAsBoolean ( GetSummary ( sTotal ; Quarter ) )

• ###### 7. Re: Need to determine the 12th of the month

Michael, maybe a picture will help to explain what the calc needs to be.

This is the detail:

This is the Summary:

If I can't get this to work, it can be done manually, but trying to avoid that route.

• ###### 8. Re: Need to determine the 12th of the month

I am afraid that doesn't tell me much. How exactly did you get "a summary of all the one's" you mentioned earlier? I thought you had a sub-summary part by quarter, but it doesn't look like you do.

• ###### 9. Re: Need to determine the 12th of the month

Michael, I finally got it working.

Using your original formula, I incorporated the Quarter and Month 1,2 or 3 of the quarter and then created summary fields.

It works perfect.

New working formula:

for Quarter:

QTR_payperiod

If(GetAsNumber(QTR Month_payperiod) = 1 or GetAsNumber(QTR Month_payperiod) = 2 or GetAsNumber(QTR Month_payperiod) = 3 ; 1; If(GetAsNumber(QTR Month_payperiod) = 4 or GetAsNumber(QTR Month_payperiod) = 5 or GetAsNumber(QTR Month_payperiod) = 6 ; 2; If(GetAsNumber(QTR Month_payperiod) = 7 or GetAsNumber(QTR Month_payperiod) = 8 or GetAsNumber(QTR Month_payperiod) = 9 ; 3; If(GetAsNumber(QTR Month_payperiod) = 10 or GetAsNumber(QTR Month_payperiod) = 11 or GetAsNumber(QTR Month_payperiod) = 12 ; 4 ;""))))

for Month:

QTR_Month_payperiod

Month(For Pay Period)

Then these calcs are used in the report:

Q12th of the month 1:

If ( QTR Month_payperiod=1 ; WeekOfYear ( For Pay Period ) =  WeekOfYear ( Date ( Month (For Pay Period ) ; 12 ; Year ( For Pay Period ) ) ) ; 0)

Q12th of the month 2:

If ( QTR Month_payperiod=2 ; WeekOfYear ( For Pay Period ) =  WeekOfYear ( Date ( Month (For Pay Period ) ; 12 ; Year ( For Pay Period ) ) ) ; 0)

Q12th of the month 3:

If ( QTR Month_payperiod=3 ; WeekOfYear ( For Pay Period ) =  WeekOfYear ( Date ( Month (For Pay Period ) ; 12 ; Year ( For Pay Period ) ) ) ; 0)

Then I added summary fields for each of the Q12th of the months for the summary report.

This is one way, and there is probably a less verbose way, but hey it works!

Thank you so MUCH!

1 of 1 people found this helpful