+ Reply to Thread
Results 1 to 3 of 3

How To Calculate future dates on the business day preceeding the 15th of each month

  1. #1
    Registered User
    Join Date
    09-11-2009
    Location
    USA
    MS-Off Ver
    Excel 2007
    Posts
    22

    How To Calculate future dates on the business day preceeding the 15th of each month

    Sometimes my due dates need to be on the 15th of the month, for which DLL and Donkey gave me:

    =DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)

    This works great except sometimes the 15th is on a Sunday which means I need the result to be the 13th, and sometimes it's on a Saturday which means I need the result to be the 14th.

    FYI: I am a n00b..
    Last edited by HOWTOEXCEL; 09-16-2009 at 07:23 PM.

  2. #2
    Registered User
    Join Date
    09-16-2009
    Location
    US
    MS-Off Ver
    Excel 2003
    Posts
    1

    Re: How To Calculate future dates on the business day preceeding the 15th of each mon

    Well you can do this assuming your function is in A1, but it returns the excel code for the date. Not sure how to get that back into a mm/dd/yy format.

    =IF(WEEKDAY(A1)=1,A1-2,IF(WEEKDAY(A1)=7,A1-1,A1))

  3. #3
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: How To Calculate future dates on the business day preceeding the 15th of each mon

    I thought I answered this one already.......

    You can just apply WORKDAY, i.e.

    =WORKDAY(DATE(YEAR(TODAY()), MONTH(TODAY()) + (DAY(TODAY()) > 15), 15)+1,-1)

    although if you are using WORKDAY you could also use EOMONTH, i.e.

    =WORKDAY(EOMONTH(TODAY()-15,0)+16,-1)

    Note: I'm assuming you have access to functions WORKDAY, EDATE, EOMONTH which are part of Analysis ToolPak add-in...

+ 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