# Dates, Correct proportional days per month? If Statements?

1. ## Dates, Correct proportional days per month? If Statements?

Hi, I am trying to calculate commissions for certain people. Commissions are calculate based on how the company performs as a whole "Bookings". When that person leaves "End Date",

I want the calculation to happen as a proportion of that month. In the example I have, I would like the calculation to happen for May for only the days May 1-15 for example. Is there a universal equaction that I could use in cells X through AI? I would appreciate your help!

Thanks

2. ## Re: Dates, Correct proportional days per month? If Statements?

Check This out if this could help.
PHP Code:
``` =IFERROR(IF(AND(E\$25>=\$F\$20,E\$25<=\$F\$21),(INDEX(\$C\$9:\$P\$13,MATCH(\$C26,\$C\$9:\$C\$13,0),MATCH(E\$25,\$C\$9:\$P\$9,0))*\$J\$20),0),"Out of Range")  ```

3. ## Re: Dates, Correct proportional days per month? If Statements?

thanks. it still doesn't seem to calculated anything for May

4. ## Re: Dates, Correct proportional days per month? If Statements?

@ misterv

I may not have interpreted the instructions correctly.
All your header dates return the end of the months. 5/31/2019 does not fit between 5/1/2019 and 5/15/2019. Also Y4 was set for the first of the year.

In the attached I took the liberty changing Y4 to 5/1/2019, the January header dates to 1/1/2019 and the equations in the headers to EDATE in order to return 1st of month across all the headers. All those dates now fit between the lower and upper boundaries in Y4:Y5 depending upon which month is calculated.

Then in X10 and filled through X10:AI13 this formula.
Formula:
`Please Login or Register  to view this content.`

5. ## Re: Dates, Correct proportional days per month? If Statements?

If on the other hand you want all proportional monthly figures from the 1st of the year to 5/15 try this.

Find Y4 reset to 1/1/2019 in this file.

In the attached the date headers are not the issue they were in my previous post. Any date in those months will work with this formula.

In X10 filled down and across.
Formula:
`Please Login or Register  to view this content.`

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1