Finding dates within a column

[Throughstream]

Hi I have column "B" containing various dates i.e 04/09/2018 or the word blank. What I'd like to do is have a formula/vba that finds the first date in column B and displays it in Cell AO1 and the last date found in Column B in cell AO2.

Many thanks in advance

Dave

[AMoreno]

Please attach a sample workbook for better help

Go Advanced (bottom of thread)
Additional Options Box (bottom of post reply box)
Attachments, click manage attachments
Upload from computer or wherever, click Upload
Close window,
Then Submit Reply

From the description given, the use of the OFFSET function may be what you need

[Throughstream]

Sure I've attached an example. so the first date would be 03/09/2018 and the last would be 12/03/2018


Thank you in advance.

[AMoreno]

Would all of column B have dates filled out all the way down?

Are the blanks actual dates? or just empty cells?

[Throughstream]

Hi sorry not sure what you mean by the first question, column B would have dates or the word “blank” down to about B30000. The blanks are just the word blank exactly as in the attachment.

Cheers

[alansidman]

Here is a VBA solution for you.

Please Login or Register  to view this content.

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

[Throughstream]

Great exactly what I needed.

Many thanks

Cheers

[Pete_UK]

You can use this array* formula in cell AO3:

=INDEX(B1:B220,MIN(IF(ISNUMBER(B1:B220),ROW(B1:B220))))

*Note an array formula needs to be committed using the key combination Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

You can have this non-array formula in AO4:

=LOOKUP(9E9,B:B)

Format both cells as a date in the style you prefer.

Hope this helps.

Pete

[Throughstream]

You can use this array* formula in cell AO3:

=INDEX(B1:B220,MIN(IF(ISNUMBER(B1:B220),ROW(B1:B220))))

*Note an array formula needs to be committed using the key combination Ctrl-Shift-Enter (CSE) instead of the usual < Enter >.

You can have this non-array formula in AO4:

=LOOKUP(9E9,B:B)

Format both cells as a date in the style you prefer.

Hope this helps.

Pete

Thank you, I hadn't decided if I was going to use a macro doc or not so very useful to have both options.

Cheers

[Pete_UK]

You're welcome - thanks for the rep.

Pete