Find date

[linkn00]

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>

 

[salim hasan]

supposing your data in range (A2:G5) and i2=a
in cells I4 and I5 respectively those tow array formulas

=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,""))))

	A	B	C	D	E	F	G	H	I
2	sun	mon	tue	wed	thu	fri	sat		a
3	26-Aug	27-Aug	28-Aug	29-Aug	30-Aug	31-Aug	1-Sep
4	a	b	a	a	a	a	a		26-Aug
5	c	a	d	b	c	a	c		1-Sep

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula 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,""))))} <thead> </thead><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>

 

[linkn00]

supposing your data in range (A2:G5) and i2=a
in cells I4 and I5 respectively those tow array formulas

=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,""))))

	A	B	C	D	E	F	G	H	I
2	sun	mon	tue	wed	thu	fri	sat		a
3	26-Aug	27-Aug	28-Aug	29-Aug	30-Aug	31-Aug	1-Sep
4	a	b	a	a	a	a	a		26-Aug
5	c	a	d	b	c	a	c		1-Sep

<tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula 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]

You can also use this Macro (for simple)

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]

supposing your data in range (A2:G5) and i2=a
in cells I4 and I5 respectively those tow array formulas

=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,""))))

	A	B	C	D	E	F	G	H	I
2	sun	mon	tue	wed	thu	fri	sat		a
3	26-Aug	27-Aug	28-Aug	29-Aug	30-Aug	31-Aug	1-Sep
4	a	b	a	a	a	a	a		26-Aug
5	c	a	d	b	c	a	c		1-Sep

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula 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,""))))} <thead> </thead><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>