VBA or formula to record the highest date in a row

This might be a formula or a macro/VBA - I have a feeling it will be VBA.

I have 5 columns in a sheet; A, D, G, J and M. These cells will be changed every so often. They will contain dates.
So for example cells B3 could contain 1/3/13 and then a few weeks later be changed to number 1/4/13.

I would like a formula/macro/VBA code that will tell me the 'highest' date every time the figure in A, D, G, J and M changes.

So for example A3 is 1/3/13 then it is changed to 1/4/13, so the most recent date is 1/4/13
Then cell E3 is changed to 13/3/13, so the 'highest date' is still 1/4/13 - no change
Then B3 is changed again to 3/5/13, so the new most recent date is 3/5/13 and so on.

Example.xls

I am happy for this to be one code or formula that calculates all cells, or for it to be 5 different codes; one for each cell. This will be over 10,000 rows, so it will need to either work on all rows in the sheet, or allow copying to all rows.

An example is attached and I would appreciate any help.

Thanks in advance.

Put this array* formula in Q4:

=MAX(IF((A4:O4>0)*(MOD(COLUMN(A4:O4),3)=1),A4:O4))

then copy down.

*NOTE: An array formula needs to be committed using the key combination of Ctrl-Shift-Enter (CSE) rather than the usual <Enter>

Hope this helps.

Pete

Maybe:

=MAX(A4:P4)

Format Q as date

Originally Posted by JOHN H. DAVIS

Maybe:

=MAX(A4:P4)

Format Q as date

This should work, yes. There'll be other data in the row, such as names customer numbers, but it seems to ignore these and just pull out the dates. Thanks very much to you both.

If the number of items can be in the region of 40-odd thousand, then =MAX(A4:P4) could give the wrong results.

Pete

Thanks Pete_UK - the total items are only ever in the low hundreds, so this is good. I just modified it so that it only looks at the 5 cells on each row that contain a date. Can't believe I didn't think of it before tbh.

So for example MAX(A6,D6,G6,J6,M6)

Thanks everybody.

You're welcome. Glad you have a solution.