+ Reply to Thread
Results 1 to 6 of 6

Date Formula for days of the month, new dates starting on the 2nd of the month.

  1. #1
    Registered User
    Join Date
    11-15-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    3

    Date Formula for days of the month, new dates starting on the 2nd of the month.

    I have a workbook that imports reporting data from the previous day, and puts it into the cell for the currrent day -1. The first cell automatically references the first day of the current month. The date changes to the new month on the first, but my report needs to hold on to the previous month until after it runs on the 1st.

    What formula can I use that will make the first cell equal to the first day of the current month AFTER the first of the month?

    In English it would read "If day > 1 then DATE(YEAR(TODAY()),MONTH(TODAY()),1)" or "If day >= 2" whichever makes the most sense.
    The rest of the cells are equal to the previos cell + 1, so I only need the formula for the top cell.

  2. #2
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,786

    Re: Date Formula for days of the month, new dates starting on the 2nd of the month.

    Hi,

    an attempt

    =DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY()=1),1)

    Regards
    -----------------------------------------------------

    At Excelforum, you can say "Thank you!" by clicking the star icon ("Add Reputation") below the post.

    Please, mark your thread [SOLVED] if you received your answer.

  3. #3
    Forum Guru Ace_XL's Avatar
    Join Date
    06-04-2012
    Location
    UAE
    MS-Off Ver
    2016
    Posts
    6,067

    Re: Date Formula for days of the month, new dates starting on the 2nd of the month.

    Try

    =EOMONTH(A1,IF(DAY(A1)=1,-2,-1))+1
    or
    =EOMONTH(A1,-1-(DAY(A1)=1))+1
    Life's a spreadsheet, Excel!
    Say thanks, Click *

  4. #4
    Registered User
    Join Date
    11-15-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Date Formula for days of the month, new dates starting on the 2nd of the month.

    Sorry, I posted my reply in the wrong place. I'll try again.

  5. #5
    Registered User
    Join Date
    11-15-2013
    Location
    Phoenix, AZ
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: Date Formula for days of the month, new dates starting on the 2nd of the month.

    Quote Originally Posted by CANAPONE View Post
    Hi,

    an attempt

    =DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY()=1),1)

    Regards
    This generated an error in the formula. I don't see what the error is. Any thoughts?

  6. #6
    Forum Expert
    Join Date
    12-03-2009
    Location
    Florence, Italy
    MS-Off Ver
    Excel 2010
    Posts
    1,786

    Re: Date Formula for days of the month, new dates starting on the 2nd of the month.

    Hi again

    sorry: lost a parenthesis from Italian traduction

    =DATE(YEAR(TODAY()),MONTH(TODAY())-(DAY(TODAY()=1)),1)


    Regards

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Formula needed to calculate working days in specified month between two dates
    By webfeet2 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 10-24-2013, 02:19 AM
  2. [SOLVED] Calendar Formula- Date given/add 90 days/ defult - 1st day next month
    By Gower Girl in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 07-24-2013, 06:21 PM
  3. [SOLVED] Auto populate cells from data in a 6 month range starting with the current month
    By ecarnley349 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-21-2012, 07:32 PM
  4. [SOLVED] Days off formula based on last date of the month
    By farrukh in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 07-02-2012, 07:12 AM
  5. Dates - Several Days In a month to month only
    By Andy_Pimp in forum Excel General
    Replies: 1
    Last Post: 02-28-2006, 07:15 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1