Finding a date in a column

[MrTreasury]

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

 

[steve the fish]

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?

 

[steve the fish]

Re: Hello board - finding a date in a column

Oh april doesnt have 31 days but you know what i mean i hope!

 

[MrTreasury]

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.

 

[steve the fish]

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?

 

[MrTreasury]

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.

 

[steve the fish]

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.

 

[MARK858]

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?

 

[MrTreasury]

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.

 

[MARK858]

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
	A	B
1	01/01/2019	Thursday 31/01/2019
2	01/02/2019	Thursday 28/02/2019
3	01/03/2019	Friday 29/03/2019
4	01/04/2019	Tuesday 30/04/2019
5	01/05/2019	Friday 31/05/2019
6	01/06/2019	Friday 28/06/2019
7	01/07/2019	Wednesday 31/07/2019
8	01/08/2019	Friday 30/08/2019
9	01/09/2019	Monday 30/09/2019
10	01/10/2019	Thursday 31/10/2019
11	01/11/2019	Friday 29/11/2019
12	01/12/2019	Tuesday 31/12/2019
13	01/01/2020	Friday 31/01/2020
14	01/02/2020	Friday 28/02/2020
15	01/03/2020	Tuesday 31/03/2020
16	01/04/2020	Thursday 30/04/2020
17	01/05/2020	Friday 29/05/2020
18	01/06/2020	Tuesday 30/06/2020
19	01/07/2020	Friday 31/07/2020
20	01/08/2020	Monday 31/08/2020
21	01/09/2020	Wednesday 30/09/2020
22	01/10/2020	Friday 30/10/2020
23	01/11/2020	Monday 30/11/2020
24	01/12/2020	Thursday 31/12/2020
25	01/01/2021	Friday 29/01/2021
26	01/02/2021	Friday 26/02/2021
27	01/03/2021	Wednesday 31/03/2021
28	01/04/2021	Friday 30/04/2021
Sheet3