Finding a date in a column
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Finding a date in a column
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    May 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Finding a date in a column

    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

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,556
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default 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?
    Looking for opportunities

  3. #3
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,556
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default Re: Hello board - finding a date in a column

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

  4. #4
    New Member
    Join Date
    May 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hello board - finding a date in a column

    Quote Originally Posted by steve the fish View Post
    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.

  5. #5
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,556
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default 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?
    Looking for opportunities

  6. #6
    New Member
    Join Date
    May 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hello board - finding a date in a column

    Quote Originally Posted by steve the fish View Post
    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.

  7. #7
    Board Regular
    Join Date
    Oct 2009
    Location
    Midlands, UK
    Posts
    7,556
    Post Thanks / Like
    Mentioned
    18 Post(s)
    Tagged
    3 Thread(s)

    Default 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.
    Looking for opportunities

  8. #8
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,996
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default 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?
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

  9. #9
    New Member
    Join Date
    May 2019
    Posts
    11
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Hello board - finding a date in a column

    Quote Originally Posted by steve the fish View Post
    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
    Quote Originally Posted by MARK858 View Post
    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.

  10. #10
    Board Regular MARK858's Avatar
    Join Date
    Nov 2010
    Location
    Southern England
    Posts
    10,996
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    2 Thread(s)

    Default 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.

    Sheet3

    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

    Spreadsheet Formulas
    CellFormula
    B1=WORKDAY(EOMONTH(A1,0)+1,-1)


    Excel tables to the web >> Excel Jeanie HTML 4
    Test VBA on a copy of your data (remember you can't normally reverse the action)

    Please follow the forum Rules and Guidelines & please use Code tags around your code i.e. [CODE]your code[/CODE]

    To post a screenshot try one of these links
    MrExcel HTML Maker, RoryA addin (Win & Mac) or Borders-Copy-Paste

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •