Finding a date in a column

MrTreasury

New Member
Joined
May 17, 2019
Messages
11
Hello forum,

The title is a bit misleading. The dates I'm looking for are the last days of a month, however, EOMONTH won't work in this instance as the data is financial, so as such the last day of the month could be 29th April for example.

I'm trying to find the most efficient way of searching said column for the last day of the month for each month, across a few years. I haven't written any code for it yet, as I'm really unsure what I could do. I'm looking for someone to write a bunch of code, but maybe some pointers and i can try and learn from that way.

Any advice would help. Apologies in advance if this is vague.

Thanks,

Mr T
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Re: Hello board - finding a date in a column

You havent really suggested how anyone would know what is your last day of the month. Why is it the 29th? Is it because the 30th and 31st are weekend days?
 
Upvote 0
Re: Hello board - finding a date in a column

Oh april doesnt have 31 days but you know what i mean i hope!
 
Upvote 0
Re: Hello board - finding a date in a column

You havent really suggested how anyone would know what is your last day of the month

Because of the dataset, I'm not sure which ones are either; hence the efficient way to find them. The general idea is that these are to be a sheet that's updated regularly. So when a new month is added, the code then locates the month. It may well just be easy to add it manually, but I was hoping this sheet is not really touched.

Why is it the 29th? Is it because the 30th and 31st are weekend days?
As stated, it's financial data, so yes; i chose April as just an example. If the 31st March fell on a Saturday, then 30th March would be the last day of the month as the data we're using doesn't cover weekends.

I'm just trying to explore the concept at the moment, so it may turn out to be either, not possible; or overly complicated code and I'll just lock the spreadsheet.
 
Upvote 0
Re: Hello board - finding a date in a column

I dont think you understand my question. How would anyone know which day to look for? What are the rules? Once a rule is established what is it you want to do with this date?
 
Upvote 0
Re: Hello board - finding a date in a column

I dont think you understand my question. How would anyone know which day to look for? What are the rules? Once a rule is established what is it you want to do with this date?
Your question has been answered. I don't think you understand the post.

My data is is financial, so there is no data on weekends; as the example in my previous post states. I need to find these dates in a column and pull them out; a vlookup would then be used to find a row ID. But these elements aren't necessary in the grand scheme of things. All I'm looking for, is effectively a search function based on a parameter of being the last day for that month.
 
Upvote 0
Re: Hello board - finding a date in a column

Ok so i read the first post again. We need to look for the last day of the month but it isnt necessarily the last day of the month. I cant see anywhere where you have stated how you would deduce this date. Im afraid its as clear as mud to me.
 
Upvote 0
Re: Hello board - finding a date in a column

MrTreasury, if the last day of the month is a Monday (as it is in September) what do you want returned the Monday or the previous Friday?
 
Upvote 0
Re: Hello board - finding a date in a column

Ok so i read the first post again. We need to look for the last day of the month but it isnt necessarily the last day of the month. I cant see anywhere where you have stated how you would deduce this date. Im afraid its as clear as mud to me.
You're right, it isn't clear - but the purpose was to explore if it's possible. I realise I should have said last working day
MrTreasury, if the last day of the month is a Monday (as it is in September) what do you want returned the Monday or the previous Friday?
The monday, as it would be the last working day of the month.

Effectively, I have price data in a tab called "Pricing"; so in my table in column A of "Pricing" I would have daily data (exc. weekends), on a column, for argument's sake, on column C which is the row ID, simply =ROW().

In another tab, let's call this "Data", I would store these dates in a table. The last working day of the month would then be printed into this table and a simple vlookup would pull the row ID that can be used later in a separate and unrelated macro.

That's the idea, though I'm thinking it may just be easier to manually filter and copy. I'm in control of the document, so any errors would be my fault.
 
Upvote 0
Re: Hello board - finding a date in a column

Just going out for a few hours so no time to write any VBA but if you were to do it with formula I think the below works so hopefully you can adapt the principle.

Excel Workbook
AB
101/01/2019Thursday 31/01/2019
201/02/2019Thursday 28/02/2019
301/03/2019Friday 29/03/2019
401/04/2019Tuesday 30/04/2019
501/05/2019Friday 31/05/2019
601/06/2019Friday 28/06/2019
701/07/2019Wednesday 31/07/2019
801/08/2019Friday 30/08/2019
901/09/2019Monday 30/09/2019
1001/10/2019Thursday 31/10/2019
1101/11/2019Friday 29/11/2019
1201/12/2019Tuesday 31/12/2019
1301/01/2020Friday 31/01/2020
1401/02/2020Friday 28/02/2020
1501/03/2020Tuesday 31/03/2020
1601/04/2020Thursday 30/04/2020
1701/05/2020Friday 29/05/2020
1801/06/2020Tuesday 30/06/2020
1901/07/2020Friday 31/07/2020
2001/08/2020Monday 31/08/2020
2101/09/2020Wednesday 30/09/2020
2201/10/2020Friday 30/10/2020
2301/11/2020Monday 30/11/2020
2401/12/2020Thursday 31/12/2020
2501/01/2021Friday 29/01/2021
2601/02/2021Friday 26/02/2021
2701/03/2021Wednesday 31/03/2021
2801/04/2021Friday 30/04/2021
Sheet3
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,938
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top