Home

Formula to get latest date among dates

%3CLINGO-SUB%20id%3D%22lingo-sub-676760%22%20slang%3D%22en-US%22%3EFormula%20to%20get%20latest%20date%20among%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676760%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EHere%20i've%20attached%20my%20which%20has%20GRN%20details.%201st%20tab%20has%20GRN%20date%20wise%20qty%20details%20and%20second%20tab%20has%20closing%20stock%20details.%20i%20need%20to%20get%20latest%20GRN%20date%20and%20qty%20to%20second%20tab%20(Closing%20Stock).%20can%20any%20one%20help%20for%20this%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-676760%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAdmin%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EDeveloper%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%20Online%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETraining%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-676860%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20get%20latest%20date%20among%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676860%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20get%20the%20Latest%20GRN%20Date%20by%20using%20%3CA%20href%3D%22https%3A%2F%2Fsupport.office.com%2Fen-ie%2Farticle%2Fmaxifs-function-dfd611e6-da2c-488a-919b-9b6376b28883%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3EMAXIFS%20function%3C%2FA%3E%2C%20but%20before%20that%2C%20you%20have%20to%20convert%20the%20dates%20in%20GRN%20Details%20sheet%20to%20numeric%20dates%2C%20it's%20currently%20in%20text%20format%2C%20please%20see%20this%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FExcel%2FExcel-Date-formatting%2Fm-p%2F266291%22%20target%3D%22_blank%22%3Etopic%3C%2FA%3E%20to%20learn%20more%20about%20it.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20is%20the%20MAXIFS%20formula%3A%3C%2FP%3E%3CPRE%3E%3DMAXIFS(Table1%5BGRN%20Date%5D%2CTable1%5BItem%20Code%5D%2C%5B%40%5BItem%20Code1%5D%5D)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWith%20regards%20to%20the%20Latest%20GRN%20Qty%2C%20I%20suggest%20this%20array%20formula%3A%3C%2FP%3E%3CPRE%3E%3DINDEX(Table1%5BSum%20of%20Grn%20Qty%5D%2CINDEX(MATCH(%5B%40%5BItem%20Code1%5D%5D%26amp%3B%5B%40%5BLatest%20GRN%20Date%5D%5D%2CTable1%5BItem%20Code%5D%26amp%3BTable1%5BGRN%20Date%5D%2C0)%2C))%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20find%20the%20solution%20in%20the%20attached%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-676877%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20get%20latest%20date%20among%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676877%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F35679%22%20target%3D%22_blank%22%3E%40Haytham%20Amairah%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eits%20nice.%20but%20when%20click%20on%20cell%20its%20going%20wrong.%20how%20should%20enter%20this%20maxifs%20formula%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-676943%22%20slang%3D%22en-US%22%3ERe%3A%20Formula%20to%20get%20latest%20date%20among%20dates%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-676943%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F329467%22%20target%3D%22_blank%22%3E%40Ravindu94%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhich%20version%20of%20Excel%20do%20you%20have%3F%3C%2FP%3E%3CP%3EMAXIFS%20function%20works%20only%20in%20Excel%202019%20or%20Office%20365%2C%20if%20you%20haven't%20one%20of%20them%2C%20you%20will%20get%20the%20%23NAME%20error!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20you%20have%20an%20earlier%20version%20of%20Excel%2C%20please%20try%20this%20alternative%20formula%3A%3C%2FP%3E%3CPRE%3E%3DAGGREGATE(14%2C6%2C(A2%3D'GRN%20Details'!%24A%242%3A%24A%241505)*'GRN%20Details'!%24B%242%3A%24B%241505%2C1)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20that%20helps%3C%2FP%3E%3C%2FLINGO-BODY%3E
Ravindu94
Contributor

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

@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

@Haytham Amairah 

 

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

@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
Tabs and Dark Mode
cjc2112 in Discussions on
21 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
13 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies