Date range cell population

benchmarck

New Member
Joined
Feb 11, 2003
Messages
43
I need a formula for three seperate cells that will populate the data from a 4th cell provided the date given is within a range specified for each of the three. I've attempted a snapshot for better explanation.

Thanks all!

EscelFormula.gif

[/url]
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Do you want the formula for cells c5, d5 and e5?

If so you wrote them out fine in words and this is what it looks like in excel

if(countif(range1,b5)>1,a5,"")

Basically the countif counts the number of times that the value in b5 appears in range 1. The if statements simply checks to see if that is >0 and if it is sets the value to a5, otherwise sets it equal to blank.

Hope that helps.

Hayden
 
Upvote 0
Let me re-state where range 1 is a given date in cell J1 and range 2 is a later date in K1. I'm not sure how to set an early and late date range so I will opt for a "later than" condition.
 
Upvote 0
Try this then

If(and(b6>=J1,b6<=k1),a5,"")

If the date in b6 greater than or equal to the date in j1 AND the date in b6 is less than or equal to the date in k1, then a5, otherwise blank.

You can remove the = sign if you only want less than or greater than.

Hayden
 
Upvote 0
For some reason no matter what date I put in B5 the result is blank. Your answer refered to row 6. Is there any significance to that? I took it as a type o.
 
Upvote 0
=IF(AND(b5>=K6,b5<=K7),a5,"") This acctualy works when k6 and k7 set the early and late dates of the range. To add the formula to the other cells I used k8 & k9 for the second range not overlapping the dates.

Thanks
 
Upvote 0

Forum statistics

Threads
1,217,328
Messages
6,135,912
Members
449,971
Latest member
Hughesy52

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top