Date Conditional Formula

[Peteor]

Hello! This formula is beating me... Cell A25 contains the date 11/7/2019 and Cell B25 contains the date 11/20/2019. I also have the following starting at H2 (H2 contains the date 11/7/2019):

[TABLE="width: 100"]
<tbody>[TR]
[TD]11/7/2019[/TD]
[/TR]
[TR]
[TD]11/21/2019[/TD]
[/TR]
[TR]
[TD]12/5/2019[/TD]
[/TR]
[TR]
[TD]12/19/2019[/TD]
[/TR]
[TR]
[TD]1/2/2020[/TD]
[/TR]
[TR]
[TD]1/16/2020[/TD]
[/TR]
[TR]
[TD]1/30/2020[/TD]
[/TR]
[TR]
[TD]2/13/2020[/TD]
[/TR]
[TR]
[TD]2/27/2020[/TD]
[/TR]
[TR]
[TD]3/12/2020[/TD]
[/TR]
[TR]
[TD]3/26/2020[/TD]
[/TR]
[TR]
[TD]4/9/2020[/TD]
[/TR]
[TR]
[TD]4/23/2020[/TD]
[/TR]
</tbody>[/TABLE]

I need to do the following--------- if(a date in range H2:H14 falls within or is equal to A25 and B25,H20,"")

Can anyone out there help me?

 

[alansidman]

Option Explicit


Sub Peteor()
    Dim crit1 As Date: crit1 = Range("A25")
    Dim crit2 As Date: crit2 = Range("B25")
    Dim c As Range
    Dim rng As Range
    Set rng = Range("H2:H14")
    For Each c In rng
        If c >= crit1 And c <= crit2 Then
            Range("H20") = c
        End If
    Next c


End Sub

 

[Peteor]

I was hoping to accomplish this as a formula in Excel rather than a Sub in VBA. Is that ok?

 

[DanteAmor]

Try this

<table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:79.84px;" /><col style="width:79.84px;" /><col style="width:80.79px;" /><col style="width:76.04px;" /><col style="width:76.04px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >H</td><td >I</td><td >J</td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td style="text-align:right; ">07/nov/2019</td><td >some</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td style="text-align:right; ">19/nov/2019</td><td >some</td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td style="text-align:right; ">05/dic/2019</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td style="text-align:right; ">19/dic/2019</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td style="text-align:right; ">02/ene/2020</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >7</td><td > </td><td > </td><td style="text-align:right; ">16/ene/2020</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >8</td><td > </td><td > </td><td style="text-align:right; ">13/feb/2020</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >9</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >19</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >20</td><td > </td><td > </td><td >some</td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >21</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >22</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >23</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >24</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >25</td><td style="text-align:right; ">07/nov/2019</td><td style="text-align:right; ">20/nov/2019</td><td > </td><td > </td><td > </td></tr><tr style="height:19px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >26</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b></b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >I2</td><td >=IF(AND(H2>=$A$25,H2<=$B$25),$H$20,"")</td></tr></table></td></tr></table>

 

[alansidman]

[COLOR=#333333]I was hoping to accomplish this as a formula in Excel rather than a Sub in VBA. Is that ok?[/COLOR]

I'm curious. Why?