Formula to get latest date among dates

[Ravindu94]

Hi,

Here i've attached my which has GRN details. 1st tab has GRN date wise qty details and second tab has closing stock details. i need to get latest GRN date and qty to second tab (Closing Stock). can any one help for this?

[#2]

@Ravindu94

Hi,

You can get the Latest GRN Date by using MAXIFS function, but before that, you have to convert the dates in GRN Details sheet to numeric dates, it's currently in text format, please see this topic to learn more about it.

This is the MAXIFS formula:

=MAXIFS(Table1[GRN Date],Table1[Item Code],[@[Item Code1]])

With regards to the Latest GRN Qty, I suggest this array formula:

=INDEX(Table1[Sum of Grn Qty],INDEX(MATCH([@[Item Code1]]&[@[Latest GRN Date]],Table1[Item Code]&Table1[GRN Date],0),))

Please find the solution in the attached file.

Hope that helps

[#3]

@Haytham Amairah 

its nice. but when click on cell its going wrong. how should enter this maxifs formula

[#4]

@Ravindu94

Which version of Excel do you have?

MAXIFS function works only in Excel 2019 or Office 365, if you haven't one of them, you will get the #NAME error!

If you have an earlier version of Excel, please try this alternative formula:

=AGGREGATE(14,6,(A2='GRN Details'!$A$2:$A$1505)*'GRN Details'!$B$2:$B$1505,1)

Hope that helps