Select the earliest date

[PRW]

Could any body please help me with a formula to pick a first and last date from two lists of dates.

I have attached a spreadsheet to show the exact problem.

Thanks

Peter

[Flintstone]

PRW:

Paste this formual in cell F9 and copy down.

Please Login or Register  to view this content.

Paste this formula in cell DP9 and copy down.

Please Login or Register  to view this content.

[PRW]

Hello Flintstone.

Thanks for your help

The problem now is that I'm now getting a date code as the result, instead of the actual date.

Peter

[Flintstone]

Peter:

When you say date code, it sounds like a format problem; select cells DP9:DP15 and click Format/Format cells, the Format Cells dialog box will open make sure the Number Tab is selected and click Date in the category list.

[PRW]

Yes you were quite right, it was the format settings.

Flintstone, many thanks indeed, thats one more problem on my programme sorted out.

Peter.

[PRW]

Hello,

I wonder if any one can help me with a date problem.

I have a series of cells with dates in. and what I want to do is insert a formula to select the earliest date in the year, which will not necessarily be the earliest year so from the following examples the 04-May-08 would be selected.

17-Jun-07, 13-Sep-05, 04-May-08.

If I write MIN. in the formula I just get the earliest date in the earliest year, has anybody got any ideas.

Thanks

[PRW]

Hello,

I wonder if any one can help me with a date problem.

I have a series of cells with dates in. and what I want to do is insert a formula to select the earliest date in the year, which will not necessarily be the earliest year so from the following examples the 04-May-08 would be selected.

17-Jun-07, 13-Sep-05, 04-May-08.

If I write MIN. in the formula I just get the earliest date in the earliest year, has anybody got any ideas.

Thanks

[oldchippy]

Hi,

Here's one way, but there may well be a shorter or easier way?

With your list of dates in column A, use a couple of "helper" columns, put this in B2 and copy down

=MAX(YEAR($A$2:$A$8)) Enter as an array Ctrl+Shift+Enter to enclose the formula in { }, then in C2

=IF(A2>=DATE(VALUE(B2),1,1),A2,"") copy down

Then in D2 to get the date you want

=MIN(C2:C8)

Example attached

[daddylonglegs]

If your dates are in A1:A10 then this array formula will give you the date which is earliest in the year, irrespective of the year:

=MAX(IF(TEXT(A1:A10,"mmdd")+0= MIN(TEXT(A1:A10,"mmdd")+0),A1:A10))

confirm with CTRL+SHIFT+ENTER

Note: if the list contains two or more dates with the same day and month but in different years, e.g. 6th June 2007 and 6th June 2001, it'll select the most recent....

[PRW]

Hello Old Chippy,

Thanks for your help but the result you have given me isn't quite right.

You have now given me the earliest date in one year, I actually want the earliest date of the year from all the years in the list.

Therefore from these options 24-Jul-05, 19-Aug-06 and 07-Jan-07.

07-Jan-07 would be the answer because its the earliest date in the year. I hope this is a little easier to understand.

Thanks Peter

[PRW]

Hello daddylonglegs,

Many thanks your formula seems to work well but it has to be placed within existing formula, and I don't know where to put it, so I have inserted a small section of the spreadsheet that I'm using to show you what the situation is, hope this is ok.

In fact on the attached spreadsheet in column "F" I need the earliest date recorded and in column "G" I need the lasest date recorded.

Thanks Peter.

[daddylonglegs]

It makes it a bit more complicated if you have text and dates mixed in the same column....

See cells F12 and G12

Note: these are array formulas which need to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

[PRW]

Hello daddylonglegs,

Thanks for your help but I'm afraid we are still not quite there.

Your formula is great it does the exact job I need, but you were relating it to columns "F" and "G". What I should have said to you is that each date in Columns "F" and "G" are the result of 25 other cells in each row.

eg the date in "F4" is the result from dates in "AR4:BQ4".
eg The date in "G4" is the result from dates in "BS4:CR4".

So if you click on "F4" and "G4" this is where your formulas must be pasted and then dragged down, the problem is that I don't know where to put your formula in relation to the formula that already is in each cell.

There an added factor as I'm sure you can see. If there are arn't any dates in any of the rows "AR4:BQ10"and "BS4:CR10". The answer that has to be returned must be "None".

Thanks again, Peter.

[daddylonglegs]

OK, I see. Try this version......

[oldchippy]

Hi Peter,

Thanks for your help but the result you have given me isn't quite right.

You have now given me the earliest date in one year, I actually want the earliest date of the year from all the years in the list.

Sorry, miss read the question - common error of mine!

If there's anyone here who's good with dates - that's daddylonglegs - he's on the case - you're in luck!

[PRW]

Hello oldchippy,

Thanks for keeping on the case. I feel that daddylonglegsis nearly there. You're a great team.

All the best

Peter.

[PRW]

Hello daddylonglegs,

Column "F" seems fine but there is still an issue with column "G" that selects the latest date.

I have changed your formula in column "G4" so that the answer is taken from "BS4:CR4" and then copied it down (You accidently left the cell range from the cells that give the earliest date).

But now the latest date answers in "G4:G10" is giving me a list of answers that say 00-Jan-00 something isn't quite right, I have tried checking the formula through and can't see what the problem is.

Thanks, Peter.

[oldchippy]

Hi,

As daddy says

Note: these are array formulas which need to be confirmed with CTRL+SHIFT+ENTER so that curly braces like { and } appear around the formula in the formula bar

[daddylonglegs]

Ah, I didn't look closely enough, I didn't realise that column G used a different range.....

Formula is OK but it needs to be confirmed with CTRL+SHIFT+ENTER in order to return the correct result.

If you look at formula in F2 you'll see that it is surrounded by curly braces like { and }, this is the result of CTRL+SHIFT+ENTER

To do that....

Select cell G2, press F2 on keyboard, hold down CTRL and SHIFT keys and press ENTER. Curly braces should appear around the formula in the formula bar and you should see the correct date. Now copy formula down column

Any time you change these sort of formulas you lose the curly braces so you need to re-confirm.....

[PRW]

Hello daddylonglegs,

You are the man.

Everything works great on small section of spreadsheet that I sent you. Now I must check to see all is well on the original spreadsheet.

Thanks again, this has been a big effort on your part I really appriciate it.

Peter.