+ Reply to Thread
Results 1 to 16 of 16

nested IF formula to return a number if a date falls within a given date range

  1. #1
    Registered User
    Join Date
    04-02-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    10

    nested IF formula to return a number if a date falls within a given date range

    Hi,

    I'm new to this Forum and I'm hoping someone can help me resolve this nested formula issue.

    I'm trying to create a nested IF formula to return a number if a date falls within a given date range. The following formula works for the following start and end date if the date chosen is 3/31/13, but not subsequent months (4/30, 5/31, etc.):

    Start Date: 3/21/13 End Date: 5/31/13

    =IF(MONTH(F$71=$B$87),IF(AND(F$71>=$B$87,F$71<=$C$87),'Project Staffing Projections'!$F$41/30*(F$71-$B$87),'Project Staffing Projections'!$F$41),IF(MONTH(F$71=$C$87),'Project Staffing Projections'!$G$41,0))

    This formula accounts for the partial Staffing Projections for the month of March (10 days), but does not work for following months correctly.

    How can I rewrite this formula to show that if, let's say, 6/30/13 doesn't fall within 3/21/13 and 5/31/13, projections should be zero?

    Thanks!

  2. #2
    Forum Contributor Mayda89's Avatar
    Join Date
    09-13-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: nested IF formula to return a number if a date falls within a given date range

    Hi and welcome to the forum,

    You have used the expression below twice in your formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    instead of that, you should use this:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    If I have helped, Don't forget to add to my reputation (click on the star icon below the post).
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved).
    Use code tags when posting your code.

  3. #3
    Registered User
    Join Date
    04-02-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: nested IF formula to return a number if a date falls within a given date range

    Thank you, that worked for the 3/31/13 date!

    However, when the given date is 4/30/13, as an example, and I want to return a number based on the condition whether April is within a Start Date: 3/21/13 and End Date: 5/30/13 using the following formula, the result is returning the number based on the F41/30 * (G$71-$B$87), adding March 21 -31 partial number to the result. It should return the number located in 'Project Staffing Projections'!$F$41:

    Where B87 is the Start Date: 3/21/13
    Where C87 is the End Date: 5/30/13

    =IF(AND(G$71>=$B$87,G$71<=$C$87),'Project Staffing Projections'!$F$41/30*(G$71-$B$87),IF(MONTH(G$71)=MONTH($C$87),'Project Staffing Projections'!$G$41,0))

    How can I fix the formula for 4/30/13 and future months to return the number listed in 'Project Staffing Projections'!$F$41 without the addition of partial March?

    Thanks again!

  4. #4
    Registered User
    Join Date
    04-02-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: nested IF formula to return a number if a date falls within a given date range

    It looks like 4/30/13 and future months are working properly if the formula is set up this way:

    =IF(MONTH(G$71)=MONTH($B$87),'Project Staffing Projections'!$F$41/30*(G$71-$B$87),IF(AND(G71>=$B$87,G$71<=$C$87),'Project Staffing Projections'!$F$41,0))

    Thanks again for the tip on separating the MONTH.

  5. #5
    Registered User
    Join Date
    04-02-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: nested IF formula to return a number if a date falls within a given date range

    One last caveat:

    Assume the Start Date is 12/1/13 and End Date is 12/22/13 and the given date is 12/31/13, the formula that works well for all other months does not work for the partial months here using this formula as the 1st condition is true and the Start and End Date are in the same month:

    =IF(MONTH(O$71)=MONTH($B$132),'Project Staffing Projections'!$F$128/30*(O$71-$B$132),IF(MONTH(O71)=MONTH(C132),'Project Staffing Projections'!G128,IF(AND(O71>=$B$132,O$71<=$C$132),'Project Staffing Projections'!$F$128,0))

    Any idea how to fix this formula to account for the pro-rated 22 days and not the entire projections?

    Thanks.

  6. #6
    Forum Contributor Mayda89's Avatar
    Join Date
    09-13-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: nested IF formula to return a number if a date falls within a given date range

    Hi swissmiss,
    You have responded in 3 different posts, which makes it hard to understand your problem; and you are also not clear in your posts.
    Hence, I am having trouble understanding the final situation in your problem.

    Please summarize the key aspects of your problem in 1 single post, and upload a sample workbook which illustrates your problem.

    Cheers,

  7. #7
    Registered User
    Join Date
    04-02-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: nested IF formula to return a number if a date falls within a given date range

    Hi Mayda89,

    Sorry, I'm new to this Forum and wasn't quite sure how to create a new one.

    Attached is a sample workbook. Please refer to the highlighted cells in yellow in the Fee Summary Tab. Essentially, I'm trying to return a given number if a given date (see top sequential monthly dates (row 7) falls within the Start and End Dates listed in the Fee Summary Tab.

    The formulas listed work in some instances and doesn't work in others. For example,the cell highlighted in orange should return a partial month given that only 20 days (Start Date: 3/1/13, End Date 3/20/13) are within that particular month.

    I'd like to create a formula that work in all scenarios, for full and partial months so that if date Projections change, the formula adjust the given number.

    Hope this makes sense.

    Thanks.
    Attached Files Attached Files

  8. #8
    Forum Contributor Mayda89's Avatar
    Join Date
    09-13-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: nested IF formula to return a number if a date falls within a given date range

    Hi swissmiss,
    No problem, we'll work this out!
    I just need a clarification on this:

    Quote Originally Posted by swissmiss View Post
    For example,the cell highlighted in orange should return a partial month given that only 20 days (Start Date: 3/1/13, End Date 3/20/13) are within that particular month.
    What do you mean by the statement "it should return a partial month"?

  9. #9
    Registered User
    Join Date
    04-02-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: nested IF formula to return a number if a date falls within a given date range

    Hi Mayda89,

    The cell highlighted in orange should return Projections for 3/1/13 thru 3/20/13 as listed in the Projections Tab, cell G29, not F29. Right now, it accounts for the full month of 3/13 incorrectly. It should return only 20 days in 3/13, not the full month.

    The current formula appears to work on all other scenarios where the the Start and End Dates are broader.

    Hope this helps.

    Thanks.

  10. #10
    Forum Contributor Mayda89's Avatar
    Join Date
    09-13-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: nested IF formula to return a number if a date falls within a given date range

    Paste this formula to the orange cell, it will fix both the problem you have observed, and one that you haven't noticed.

    Formula: copy to clipboard
    Please Login or Register  to view this content.


    P.S: Don't forget to adjust the cells with absolute row reference, while copying this formula to the cells BELOW the orange cell.

    Cheers,

  11. #11
    Registered User
    Join Date
    04-02-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: nested IF formula to return a number if a date falls within a given date range

    Hi Mayda89,

    I can't see the formula here. Can you copy and paste it in the Thread?

    Thanks.

  12. #12
    Registered User
    Join Date
    04-02-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: nested IF formula to return a number if a date falls within a given date range

    Hi Mayda89,

    I pasted your formula in the orange cell and it works. However, when you apply this formula to other cells, see row 23 of the Fee Summary tab, the pasted formula does not work correctly. This is my dilemma. How do we create a formula that works for all scenarios correctly?

    Thanks.

  13. #13
    Registered User
    Join Date
    04-02-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: nested IF formula to return a number if a date falls within a given date range

    To clarify, if you refer to April and May 2013 row 23 of the Fee Summary, both cells should return the vallue listed in the Staffing Projections Tab, G41.

    However, it show an accumulation of months thru the End Date.

    Interesting and challenging. I'm not sure a formula exists to satisfy all conditions here.

  14. #14
    Forum Contributor Mayda89's Avatar
    Join Date
    09-13-2012
    Location
    New York
    MS-Off Ver
    Excel 2010
    Posts
    192

    Re: nested IF formula to return a number if a date falls within a given date range

    Sorry for the late reply.

    This should work.

    Formula: copy to clipboard
    Please Login or Register  to view this content.

  15. #15
    Registered User
    Join Date
    04-02-2013
    Location
    San Francisco
    MS-Off Ver
    Excel 2007
    Posts
    10

    Re: nested IF formula to return a number if a date falls within a given date range

    Thanks Mayda89.

    The formula works well if within a given year. However, I found that if you have different years involved, example:

    Start Date: 10/1/2013
    End Date: 02/15/2014

    The formula doesn't work. Do you know of a formula to also look at the YEAR in addition to the MONTH?

    Thanks again for all your help with this, much appreciated!

  16. #16
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,412

    Re: nested IF formula to return a number if a date falls within a given date range

    This may works:
    Please Login or Register  to view this content.
    Quang PT

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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