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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

hayden

Board Regular
Joined
Sep 23, 2005
Messages
159
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
 

benchmarck

New Member
Joined
Feb 11, 2003
Messages
43
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.
 

hayden

Board Regular
Joined
Sep 23, 2005
Messages
159
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
 

benchmarck

New Member
Joined
Feb 11, 2003
Messages
43

ADVERTISEMENT

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.
 

benchmarck

New Member
Joined
Feb 11, 2003
Messages
43
=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
 

Forum statistics

Threads
1,141,587
Messages
5,707,242
Members
421,498
Latest member
matinebi

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
Top