+ Reply to Thread
Results 1 to 5 of 5

Formula to display last day of month if current day is past X date?

  1. 12-17-2013, 06:10 AM #1
    WL2005HBD
    WL2005HBD is offline
    Registered User
    Join Date
    12-16-2013
    Location
    Melbourne, AU
    MS-Off Ver
    Excel 2003
    Posts
    12

    Formula to display last day of month if current day is past X date?

    Hey guys,

    Trying to make a formula using EOMONTH that will show the last day of the following month.

    I need it just to display in the cell the formula is in.

    So for example:

    Today is the 17th of December, the formula should know as we are past 6th of December, then return the last day of January

    If today was the 1st of January, still show 31st of January, if today was however the 6th of January, display 28th of Februaru,

    And if today was the 25th of January, display 28th of February


    Basically need the formula to know that if today is past the 6th of the current month, then display the last day of the next month, but if today if before the 6th of the current month, display the last day of that month.

    Thanks
    Reply With QuoteRegister To Reply
  2. 12-17-2013, 06:19 AM #2
    ramananhrm
    ramananhrm is offline
    Valued Forum Contributor
    Join Date
    09-15-2011
    Location
    Chennai, India
    MS-Off Ver
    Excel 2010
    Posts
    436

    Re: Formula to display last day of month if current day is past X date?

    Please try this function

    =IF(DAY(A1)<=6,EOMONTH(A1,0),EOMONTH(A1,1))
    Please click 'Add reputation', if my answer helped you.
    Reply With QuoteRegister To Reply
  3. 12-17-2013, 06:27 AM #3
    etaf
    etaf is offline
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.84 (24041420))
    Posts
    8,751

    Re: Formula to display last day of month if current day is past X date?

    this should do that

    =IF(AND(TODAY()<DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(6)), TODAY()>=DATE(YEAR(TODAY()),MONTH(TODAY()),DAY(1))),EOMONTH(TODAY(),0),EOMONTH(TODAY(),1))

    if its actually the 6th - then it will use next months date
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.
    Reply With QuoteRegister To Reply
  4. 12-17-2013, 06:44 AM #4
    Ace_XL
    Ace_XL is offline
    Forum Expert Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,074

    Re: Formula to display last day of month if current day is past X date?

    Shorter

    =EOMONTH(A1,--(DAY(A1)>=6))
    Life's a spreadsheet, Excel!
    Say thanks, Click *
    Reply With QuoteRegister To Reply
  5. 12-17-2013, 08:02 AM #5
    daddylonglegs
    daddylonglegs is offline
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Formula to display last day of month if current day is past X date?

    or this will work

    =EOMONTH(A1-5,1)

    If you want that applied to today's date you can just replace A1 with today(), i.e.

    =EOMONTH(TODAY()-5,1)
    Audere est facere
    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] Date formula with text in same cell but using previous month not current month
    By Tracyann in forum Excel General
    Replies: 5
    Last Post: 10-04-2012, 07:06 AM
  2. Formula(s) to detect and display first Wednesday of current month
    By FrancisXSlaughterry in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-05-2011, 09:09 AM
  3. Current date formula based on month
    By Renz09 in forum Excel General
    Replies: 2
    Last Post: 05-05-2006, 02:04 AM
  4. Graphing past months (totals) and current month (weekly total) in
    By Davin in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 07-27-2005, 12:05 PM
  5. [SOLVED] HELP - need to returns the current number of past month this year and ...
    By elz64 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 04-06-2005, 10:06 AM

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