+ Reply to Thread
Results 1 to 4 of 4

How to Get last trading day of selected Month and Year from a big dataset in excel

  1. 12-26-2013, 07:15 AM #1
    avisurana
    avisurana is offline
    Registered User
    Join Date
    03-01-2012
    Location
    kolkata, India
    MS-Off Ver
    MS Office 2007
    Posts
    6

    Arrow How to Get last trading day of selected Month and Year from a big dataset in excel

    HI,

    I have a data set say>> A4:A499, which covers trading days from 01/01/09 to 17/02/2102. I want a formula which finds the last day of any month and year I choose. Suppose in B1 I have written "2012" as year and B2 I have written "2" as Month. As we have the trading data till 17/02/2012, so last trading day for Feb'12 is 17/02/2012.

    Suppose in B1 I have written "2011" as year and B2 I have written "2" as Month, then it should reflect the last trading day for Feb'11, say 28/02/12.
    Please Note: It is not necessary trading day of any month starts on the first day of the month or ends on the last day of the month.


    Please find the attached file and Advice !!

    Regards,
    Avi Suranaetfsharpe.xlsx
    Reply With QuoteRegister To Reply
  2. 12-26-2013, 07:28 AM #2
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: How to Get last trading day of selected Month and Year from a big dataset in excel

    Try this array formula

    =IFERROR(LARGE(IF(A4:A497<=DATE(B1,C1+1,0),A4:A497),1),"No match")

    confirmed with CTRL+SHIFT+ENTER
    Audere est facere
    Reply With QuoteRegister To Reply
  3. 12-26-2013, 07:32 AM #3
    amit.wilson
    amit.wilson is offline
    Forum Contributor amit.wilson's Avatar
    Join Date
    07-09-2013
    Location
    Gotham
    MS-Off Ver
    Excel for Mac 2011
    Posts
    283

    Re: How to Get last trading day of selected Month and Year from a big dataset in excel

    Hello A,

    try this:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.


    Cheers
    <-- If you're happy & you know it...click the star.:-)
    Reply With QuoteRegister To Reply
  4. 12-26-2013, 07:38 AM #4
    avisurana
    avisurana is offline
    Registered User
    Join Date
    03-01-2012
    Location
    kolkata, India
    MS-Off Ver
    MS Office 2007
    Posts
    6

    Re: How to Get last trading day of selected Month and Year from a big dataset in excel

    Awesome !! Its working.

    Thanks
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Display the first date of the month when the month and year are selected
    By john dalton in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-05-2013, 11:59 AM
  2. [SOLVED] How do I display the date when a year and month are selected
    By john dalton in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-04-2013, 12:32 PM
  3. Keep month and date but change year to current year and reoccur when new year rolls over.
    By cdixon01 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-04-2013, 10:49 AM
  4. How to retrieve last month of the year selected
    By Vinothini07 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-22-2013, 03:11 AM
  5. If Column A Month and Year = ColumnB Todays Month and Year then send email
    By HACCStaff in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 04-15-2013, 12:47 AM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1