Formula to mark last entry dates by month and year

[Neavalmi]

Dear Excel Experts,

I have a range of dates in days of stock prices with the ticker symbol, open, high, close, volume. I have a list of data dump from 2000 to 2020. I want to make a formula in the last column to mark the last entry dates of each month of each year so i can make a chart in months of these stocks symbols. Once i have these last entry dates in a month and year then i will just use these data to make my chart. Thanks for your help. I have put some info also just for sample

Date Stock Open High Low Close Volume Remark
4/8/2020 2GO 10.0 9.54 9.2 9.5 20,000
4/8/2020 ABA xx
4/30/2020 2GO 10.4 xxx xx xx xxxx Last Entry
4/30/2020 xx xx xx xx xx xxx Last Entry
5/2/2020 xx
When the entry is not the last entry of the month in a year, then it is BLANK, if it is the last entry of the month in a year then remark will be "Last Entry".

Thanks for all your help.

Regards/

 

[Neavalmi]

Here's a pasted excel table

Date	STOCK_CODE	OPEN	LOW	HIGH	CLOSE	VOLUME	Column with Formula
5/4/2020​	2GO	10.98​	10.68​	11.5​	11.08​	588000​
5/4/2020​	AB	10.88​	10.88​	10.88​	10.88​	200​
4/30/2020​	2GO	10.7​	10.5​	12.2​	10.6​	1850500​	Last Entry
4/30/2020​	AB	10.9​	10.3​	10.9​	10.88​	1500​	Last Entry
4/29/2020​	2GO	9.5​	9.15​	11.3​	11.2​	1448900​
4/29/2020​	AB	10.7​	10.7​	10.7​	10.7​	4400​
4/28/2020​	2GO	9.1​	8.6​	10​	9.4​	626600​
4/28/2020​	AB	10.5​	10.5​	10.7​	10.7​	9300​
3/31/2020​	2GO	6​	6​	6.2​	6​	32700​	Last Entry
3/31/2020​	AB	10.86​	10.5​	10.86​	10.86​	17700​	Last Entry
3/30/2020​	2GO	6​	5.8​	6​	6​	16100​
3/30/2020​	AB	10.86​	10.86​	10.86​	10.86​	9100​

 

[Neavalmi]

i pasted only a sample of 2 stock code and just 3 months of data,

 

[jasonb75]

Assuming that your data is formatted as a table, try

=IF(COUNTIFS([STOCK_CODE],[@STOCK_CODE],[Date],">"&[@Date],[Date],"<="&EOMONTH([@Date],0))=0,"Last Entry","")

 

[Neavalmi]

hi jason, thank you. i formateed my data into a table but when i pasted the formula, nothing happens except for it created automatically the column where the pasted formula is.

Is there a formula that applied to a data not in a formatted table?

Many thanks.

 

[jasonb75]

Same formula, you just need to change the ranges to match your data. There was a typo that I missed in the formula, so that might be why it didn't work.

=IF(COUNTIFS([STOCK_CODE],[@[STOCK_CODE]],[Date],">"&[@Date],[Date],"<="&EOMONTH([@Date],0))=0,"Last Entry","")

 

[Neavalmi]

Was able to run the formula but it seems not calculating correctly.

 

[jasonb75]

I can see nothing in your screen capture to say that the count is wrong. Based on what you have shown, I would say that there are no later entries in May for AUB, CA or CAT, but that there are later entries for everything else. I believe that this is what you asked for.