+ Reply to Thread
Results 1 to 4 of 4

Populate 15th of month or next business day

  1. #1
    Registered User
    Join Date
    07-09-2013
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2010
    Posts
    2

    Populate 15th of month or next business day

    Hi -

    My apologies if this a repeat of a thread i started earlier, but I don't see it any where in the forum.

    I am creating a somewhat dynamic calendar spreadhseet for a user group that has functions that need to be performed on very specific business days (ex. archive data on business day 10). I've got this working. I have the first day of the month in A1 and in column C the business days are populating. (Thank you Excel Forum!)

    The group also has some functions that occur on specific days of the month. I would like to populate the 15th of the month or the next business day if it falls on a weekend, and the last day of the month or the previous business day if it falls on a weekend.

    Thank you in advance for your help.
    Last edited by tntmm6; 07-09-2013 at 02:53 PM.

  2. #2
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Populate 15th of month or next business day

    15th (or next business day)
    =WORKDAY(DATE(2013,1,14),1)
    This uses the 14th of the given month and adds 1 business day.

    Last day (or previous business day)
    =WORKDAY(DATE(2013,2,1),-1)
    This uses the 1st day of the NEXT month, then subtracts 1 business day.
    So if you want the last day of January, use 2 for the month.
    If you want last day of December, use 13 (YES, 13)

  3. #3
    Registered User
    Join Date
    07-09-2013
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Populate 15th of month or next business day

    Quote Originally Posted by Jonmo1 View Post
    15th (or next business day)
    =WORKDAY(DATE(2013,1,14),1)
    This uses the 14th of the given month and adds 1 business day.

    Last day (or previous business day)
    =WORKDAY(DATE(2013,2,1),-1)
    This uses the 1st day of the NEXT month, then subtracts 1 business day.
    So if you want the last day of January, use 2 for the month.
    If you want last day of December, use 13 (YES, 13)
    Thank you! Is there a way for this formula to work with the date I have in A1? For example, in this month's calendar I have 8/1/13 in A1, I would like 8/15/13 returned in the first formula and 8/30/13 in the other.
    Last edited by tntmm6; 07-09-2013 at 02:48 PM.

  4. #4
    Forum Guru Jonmo1's Avatar
    Join Date
    03-08-2013
    Location
    Bryan, TX
    MS-Off Ver
    Excel 2010
    Posts
    9,763

    Re: Populate 15th of month or next business day

    Try

    15th (or next business day)
    =WORKDAY(DATE(YEAR(A1),MONTH(A1),14),1)

    Last day (or previous business day)
    =WORKDAY(DATE(YEAR(A1),MONTH(A1)+1,1),-1)

  5. #5
    Registered User
    Join Date
    07-09-2013
    Location
    Tampa, Florida
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Populate 15th of month or next business day

    Perfect! Thank you so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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