# Find date

##### New Member
Hello Mr Excel,

I have a calendar that setup to look like a calendar. I need a normal look formula that could find the first day and the last day of an event.

 sun mon tue wen thu fri sat look up a 26-Aug 27-Aug 28-Aug 29-Aug 30-Aug 31-Aug 1-Sep first day a a b a a a a last day a a d c c c c 31-Aug 1-Sep 2-Sep 3-Sep 4-Sep 5-Sep 6-Sep e e b e e e e e e e e e e a

<colgroup><col span="9"><col></colgroup><tbody>
</tbody>

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### salim hasan

##### Board Regular
supposing your data in range (A2:G5) and i2=a
in cells I4 and I5 respectively those tow array formulas
Code:
``````=MIN((MIN(IF(\$A\$4:\$G\$4=\$I\$2,\$A\$3:\$G\$3,""))),(MIN(IF(\$A\$5:\$G\$5=\$I\$2,\$A\$3:\$G\$3,""))))

=MAX((MAX(IF(\$A\$4:\$G\$4=\$I\$2,\$A\$3:\$G\$3,""))),(MAX(IF(\$A\$5:\$G\$5=\$I\$2,\$A\$3:\$G\$3,""))))``````

ABCDEFGHI
2sunmontuewedthufrisata
326-Aug27-Aug28-Aug29-Aug30-Aug31-Aug1-Sep
4abaaaaa26-Aug

</tbody>
Sheet1

Array Formulas
CellFormula
I4{=MIN((MIN(IF(\$A\$4:\$G\$4=I2,\$A\$3:\$G\$3,""))),(MIN(IF(\$A\$5:\$G\$5=I2,\$A\$3:\$G\$3,""))))}
I5{=MAX((MAX(IF(\$A\$4:\$G\$4=I2,\$A\$3:\$G\$3,""))),(MAX(IF(\$A\$5:\$G\$5=I2,\$A\$3:\$G\$3,""))))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

##### New Member
supposing your data in range (A2:G5) and i2=a
in cells I4 and I5 respectively those tow array formulas
Code:
``````=MIN((MIN(IF(\$A\$4:\$G\$4=\$I\$2,\$A\$3:\$G\$3,""))),(MIN(IF(\$A\$5:\$G\$5=\$I\$2,\$A\$3:\$G\$3,""))))

=MAX((MAX(IF(\$A\$4:\$G\$4=\$I\$2,\$A\$3:\$G\$3,""))),(MAX(IF(\$A\$5:\$G\$5=\$I\$2,\$A\$3:\$G\$3,""))))``````

ABCDEFGHI
2sunmontuewedthufrisata
326-Aug27-Aug28-Aug29-Aug30-Aug31-Aug1-Sep
4abaaaaa26-Aug

<tbody>
</tbody>
Sheet1

Array Formulas
CellFormula
I4{=MIN((MIN(IF(\$A\$4:\$G\$4=I2,\$A\$3:\$G\$3,""))),(MIN(IF(\$A\$5:\$G\$5=I2,\$A\$3:\$G\$3,""))))}
I5{=MAX((MAX(IF(\$A\$4:\$G\$4=I2,\$A\$3:\$G\$3,""))),(MAX(IF(\$A\$5:\$G\$5=I2,\$A\$3:\$G\$3,""))))}

<tbody>
</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Thanks, I thought about this but this only work for 1 week. However i need to look through 17 weeks

#### salim hasan

##### Board Regular
You can also use this Macro (for simple)
Code:
``````Option Explicit
Sub Give_Max_Min()
Rem===>> Createsd By Salim Hasan On 28/8/2019
Const ro = 3
Dim min_col%, max_col%
Dim rg_4 As Range
Dim cel As Range

Set rg_4 = Range("A4:G5")
For Each cel In rg_4
If cel = Range("i2") Then
min_col = cel.Column
Exit For
End If
Next
For Each cel In rg_4
If cel = Range("i2") Then
max_col = cel.Column
End If
Next
With Range("i4:i5")
.Cells(1) = Cells(ro, min_col)
.Cells(2) = Cells(ro, max_col)
.NumberFormat = "d-mmm"
End With
End Sub``````

#### salim hasan

##### Board Regular
supposing your data in range (A2:G5) and i2=a
in cells I4 and I5 respectively those tow array formulas
Code:
``````=MIN((MIN(IF(\$A\$4:\$G\$4=\$I\$2,\$A\$3:\$G\$3,""))),(MIN(IF(\$A\$5:\$G\$5=\$I\$2,\$A\$3:\$G\$3,""))))

=MAX((MAX(IF(\$A\$4:\$G\$4=\$I\$2,\$A\$3:\$G\$3,""))),(MAX(IF(\$A\$5:\$G\$5=\$I\$2,\$A\$3:\$G\$3,""))))``````

ABCDEFGHI
2sunmontuewedthufrisata
326-Aug27-Aug28-Aug29-Aug30-Aug31-Aug1-Sep
4abaaaaa26-Aug

</tbody>
Sheet1

Array Formulas
CellFormula
I4{=MIN((MIN(IF(\$A\$4:\$G\$4=I2,\$A\$3:\$G\$3,""))),(MIN(IF(\$A\$5:\$G\$5=I2,\$A\$3:\$G\$3,""))))}
I5{=MAX((MAX(IF(\$A\$4:\$G\$4=I2,\$A\$3:\$G\$3,""))),(MAX(IF(\$A\$5:\$G\$5=I2,\$A\$3:\$G\$3,""))))}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

Replies
4
Views
93
Replies
3
Views
224
Replies
8
Views
147
Replies
2
Views
69
Replies
3
Views
182

1,130,258
Messages
5,641,162
Members
417,195
Latest member
Vishal kumar

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

### Which adblocker are you using?

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

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