Find date

linkn00

New Member
Joined
Aug 15, 2019
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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
montuewenthufrisat look upa
26-Aug
27-Aug28-Aug29-Aug30-Aug31-Aug1-Sep first day
aabaaaa last day
aad
cccc
31-Aug1-Sep2-Sep3-Sep
4-Sep5-Sep
6-Sep
eebeeee
eeeeeea


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

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

salim hasan

Board Regular
Joined
Dec 25, 2013
Messages
105
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
5cadbcac1-Sep

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><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,""))))}

<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

New Member
Joined
Aug 15, 2019
Messages
27
Office Version
  1. 365
Platform
  1. Windows
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
5cadbcac1-Sep

<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
Joined
Dec 25, 2013
Messages
105
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
Joined
Dec 25, 2013
Messages
105
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
5cadbcac1-Sep

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><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,""))))}

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

Watch MrExcel Video

Forum statistics

Threads
1,129,942
Messages
5,639,106
Members
417,072
Latest member
JaimeDee

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