Formula to look at varying number of cells and return latest date.

Copper Contributor

I have a large spreadsheet that lists order numbers in column A and the date that each parcel within that order was dock confirmed in column B.  I need to create a formula to look at the dates in column B associated with the order number and return the latest date.  There may only be one parcel in an order or there could be hundreds.  I was wanting to create a column C that would contain the formula next to the row with the order number, see below. Can anyone help?  




3 Replies




You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel for the web or Excel 2021.

latest date.png

@Debbie_Wood If you a Microsoft365 user then could try below dynamic array formula-


See the attached file.





create temp table aa as
select *,fillna(`Order`) ord from last_date;
create temp table bb as
select ord,max(`Date`) latest_date from aa group by ord;
create temp table cc as
select * from aa left join bb on aa.`Order`=bb.ord;
select colExclude[\bord\b] from cc;