Home

Latest Date with data by month

%3CLINGO-SUB%20id%3D%22lingo-sub-786199%22%20slang%3D%22en-US%22%3ELatest%20Date%20with%20data%20by%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786199%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20data%20that%20is%20listed%20weekly%20and%20I%20have%20to%20graph%20them%20into%20monthly%20reports.%20My%20problem%20is%20that%20I%20want%20to%20write%20a%20formula%20that%20takes%20the%20latest%20week%20with%20data%20by%20month.%20For%20example%20I%20have%204%20weeks%20of%20data%20from%20august%20and%202%20weeks%20of%20data%20from%20sept.%20for%20my%20value%20column%20(see%20attached)%2C%20I%20want%20a%20formula%20that%20takes%20the%20last%20week%20of%20August%20data%20and%20the%202nd%20week%20of%20Sept%20data.%20I%20have%20attached%20a%20sheet%20for%20your%20review.%20Your%20response%20will%20be%20greatly%20appreciated.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-786199%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786334%22%20slang%3D%22en-US%22%3ERe%3A%20Latest%20Date%20with%20data%20by%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786334%22%20slang%3D%22en-US%22%3E%3CP%3EHIi%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F386508%22%20target%3D%22_blank%22%3E%40kb041714%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20is%20good%20practice%20to%20store%20your%20data%20in%20an%20Excel%20Table%20(in%20the%20ribbon%2C%20Insert%20tab%2C%20and%20then%20Table)%3C%2FP%3E%3CP%3EThen%20you%20can%20easily%20reference%20your%20table%20and%20its%20column%20in%20your%20function.%20That%20way%20your%20formula%20will%20remain%20dynamic%20as%20your%20table%20expands.%3C%2FP%3E%3CP%3EYou%20could%20then%20use%20a%20VLOOKUP()%20function%20like%20this%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%3DVLOOKUP(EOMONTH(D2%2C0)%2CTable1%2C2%2CTRUE)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOr%20equally%2C%26nbsp%3Buse%20INDEX()%20and%20MATCH()%20like%20this%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-javascript%22%3E%3CCODE%3E%3DINDEX(Table1%2CMATCH(EOMONTH(D2%2C0)%2CTable1%5BDate%5D%2C1)%2C2)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20assuming%20that%20in%20cell%20D2%20you%20have%20a%20date%20that%20contains%20the%20same%20month%20and%20same%20year%20than%20the%20month%20from%20which%20you%20are%20trying%20to%20retrieve%20the%20last%20value.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20that%20correspond%20to%20what%20you%20are%20looking%20for%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-786401%22%20slang%3D%22en-US%22%3ERe%3A%20Latest%20Date%20with%20data%20by%20month%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-786401%22%20slang%3D%22en-US%22%3EFor%20D2%3A%3CBR%20%2F%3E%3DLOOKUP(2%2C1%2F%3CBR%20%2F%3E(MONTH(A%242%3AA%249)%3D8)%2C%3CBR%20%2F%3EB%242%3AB%249)%3CBR%20%2F%3EFor%20D3%3A%3CBR%20%2F%3E%3DLOOKUP(2%2C1%2F(%3CBR%20%2F%3EA%242%3AA%249%3DAGGREGATE(15%2C6%2C%3CBR%20%2F%3E1%2F(MONTH(A%242%3AA%249)%3D9)*%3CBR%20%2F%3EA%242%3AA%249%2C2))%2C%3CBR%20%2F%3EA%242%3AA%249)%3CBR%20%2F%3EFor%20E2%2C%20copied%20down%20to%20E3%3A%3CBR%20%2F%3E%3DLOOKUP(2%2C1%2F%3CBR%20%2F%3E(A%242%3AA%249%3DD2)%2C%3CBR%20%2F%3EB%242%3AB%249)%3C%2FLINGO-BODY%3E
kb041714
Occasional Visitor

I have data that is listed weekly and I have to graph them into monthly reports. My problem is that I want to write a formula that takes the latest week with data by month. For example I have 4 weeks of data from august and 2 weeks of data from sept. for my value column (see attached), I want a formula that takes the last week of August data and the 2nd week of Sept data. I have attached a sheet for your review. Your response will be greatly appreciated. 

2 Replies

HIi @kb041714 

 

It is good practice to store your data in an Excel Table (in the ribbon, Insert tab, and then Table)

Then you can easily reference your table and its column in your function. That way your formula will remain dynamic as your table expands.

You could then use a VLOOKUP() function like this

=VLOOKUP(EOMONTH(D2,0),Table1,2,TRUE)

 

Or equally, use INDEX() and MATCH() like this

=INDEX(Table1,MATCH(EOMONTH(D2,0),Table1[Date],1),2)

 

I am assuming that in cell D2 you have a date that contains the same month and same year than the month from which you are trying to retrieve the last value.

 

Does that correspond to what you are looking for?

 

For D2:
=LOOKUP(2,1/
(MONTH(A$2:A$9)=8),
B$2:B$9)
For D3:
=LOOKUP(2,1/(
A$2:A$9=AGGREGATE(15,6,
1/(MONTH(A$2:A$9)=9)*
A$2:A$9,2)),
A$2:A$9)
For E2, copied down to E3:
=LOOKUP(2,1/
(A$2:A$9=D2),
B$2:B$9)
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies