• 544K Members
• 7,844 Online
• 648K Conversations

Highlighted
Contributor

# Formula to get latest date among dates

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?

3 Replies
Highlighted

# Re: Formula to get latest date among dates

@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

Highlighted

# Re: Formula to get latest date among dates

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

# Re: Formula to get latest date among dates

@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

Related Conversations
Date format in Stream
Dave Pyett in Microsoft Stream Forum on
3 Replies
Date filtering of Bing Searches
PhilipE3 in Discussions on
2 Replies
A problem with the Zoom level of a Tab
Tavory in Discussions on
9 Replies
Setting Date in Stored Procedure
Tim Hunter in SQL Server on
1 Replies