3 Replies Latest reply on Nov 27, 2018 8:15 PM by Jennifer VonHagel

# Calculate No of Days in a month based on payperiod date

I have a start date and end date of a payperiod and we have 26 pay period for a year , But the requirement is i need to calculate no of days each pay period is taking for each month. Each pay period has 14 days.

It should be something  like this . Here i am attaching my workbook here its in 2018.1 version. I dont know if this is possible or not.

• ###### 1. Re: Calculate No of Days in a month based on payperiod date

Hi Chaitanya,

See if this works for you.

I created a Beginning Month field: DATE(DATETRUNC('month',[Beginning Date]))

Then I broke out the count of days for each pay period into two measure: One for the dates belonging in the Beginning Month:

And one for the dates beginning in the End Month:

In this calculation, I check whether Begin Month < End Month. If so, I count the days from the first day of End Month to the actual Ending Date of the Pay Period.

Then, I wrap this day count in a LOOKUP( ... ,-1) along the Beginning Month field so that the End Month Days will be moved under the next Beginning Month. For Pay Period 4, the 11 days that belong to February should normally fall under a January Begin Month, because January is the Pay Period's begin month.  But by using LOOKUP( ...,-1) I've shifted it over a month to February.

Now I put these two calculations together to count all days per Pay Period within a Begin Month:

The ZN() calculation here turns nulls to 0.  If either beg month days or end month days is null, this calculation returns null, so ZN() fixes that.

Workbook is attached.  I hope this helps.

Best,

Jennifer

1 of 1 people found this helpful
• ###### 2. Re: Calculate No of Days in a month based on payperiod date

This is what i want . You are awesome . You saved me. Thank you very much  .

• ###### 3. Re: Calculate No of Days in a month based on payperiod date

Glad it worked, happy to help!