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

1. ## 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. ## 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:
`Please Login or Register  to view this content.`

instead of that, you should use this:
Formula:
`Please Login or Register  to view this content.`

3. ## 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. ## 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. ## 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. ## 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. ## 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.

8. ## 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:

Originally Posted by swissmiss
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. ## 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. ## 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:
`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. ## 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. ## 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. ## 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. ## 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:
`Please Login or Register  to view this content.`

15. ## 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. ## 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.``

##### 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