+ Reply to Thread
Results 1 to 5 of 5

Dates, Correct proportional days per month? If Statements?

  1. #1
    Registered User
    Join Date
    11-13-2019
    Location
    NJ
    MS-Off Ver
    2013
    Posts
    34

    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
    Attached Files Attached Files

  2. #2
    Forum Contributor
    Join Date
    11-10-2017
    Location
    INDIA
    MS-Off Ver
    365
    Posts
    184

    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"
    Attached Files Attached Files
    *If you wish you click on *,a way to say ThankYou

  3. #3
    Registered User
    Join Date
    11-13-2019
    Location
    NJ
    MS-Off Ver
    2013
    Posts
    34

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

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

  4. #4
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,608

    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: copy to clipboard
    Please Login or Register  to view this content.
    Dave

  5. #5
    Forum Moderator
    Join Date
    01-21-2014
    Location
    St. Joseph, Illinois U.S.A.
    MS-Off Ver
    2007, Office 365
    Posts
    11,608

    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: copy to clipboard
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] days between 2 dates for a particular month
    By nd4spd in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-13-2018, 08:17 AM
  2. [SOLVED] Calculation of days per month for dates beginning in one month and ending in another month
    By Fahrettin in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-07-2015, 04:52 PM
  3. find time left in month.days unit between two dates(in YEAR.MONTH.DAY)
    By xcfeng95 in forum Excel Formulas & Functions
    Replies: 16
    Last Post: 02-21-2014, 12:55 PM
  4. Date Formula for days of the month, new dates starting on the 2nd of the month.
    By Kenn Jerger in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-04-2013, 01:31 AM
  5. # days in a certain month between any two dates
    By pickslides in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 11-19-2013, 11:10 PM
  6. [SOLVED] Days in particular month between two dates
    By Branno in forum Excel General
    Replies: 3
    Last Post: 06-25-2012, 06:17 PM
  7. Dates - Several Days In a month to month only
    By Andy_Pimp in forum Excel General
    Replies: 1
    Last Post: 02-28-2006, 07:15 AM

Tags for this Thread

Bookmarks

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