+ Reply to Thread
Results 1 to 5 of 5

Autofill Date with =Today() function minus weekends.

  1. #1
    Registered User
    Join Date
    09-20-2013
    Location
    Elgin IL
    MS-Off Ver
    Excel 2003
    Posts
    1

    Autofill Date with =Today() function minus weekends.

    I am creating a type of planning/production schedule. I need to have a "moving" autofill of dates that removes weekends, but I also need to do this ever 4 lines. We can build 4 units a day.
    Ex.
    =Today() (9/20) 9/20, 9/20, 9/20, 9/23, 9/23, 9/23, 9/23, 9/24, 9/24,ect.

    I can only get the weekdays to work if its only one line at a time. If I go every 4 lines, I cannot remove the weekends. Currently using Excel 2003

    Thanks everyone.

  2. #2
    Forum Guru xladept's Avatar
    Join Date
    04-14-2012
    Location
    Pasadena, California
    MS-Off Ver
    Excel 2003,2010
    Posts
    12,378

    Re: Autofill Date with =Today() function minus weekends.

    Something like:

    Please Login or Register  to view this content.
    Last edited by xladept; 09-20-2013 at 08:34 PM.
    If I've helped you, please consider adding to my reputation - just click on the liitle star at the left.

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~(Pride has no aftertaste.)

    You can't do one thing. XLAdept

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~aka Orrin

  3. #3
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Autofill Date with =Today() function minus weekends.

    Maybe this...

    Data Range
    A
    B
    1
    9/20/2013
    Fri
    2
    9/20/2013
    Fri
    3
    9/20/2013
    Fri
    4
    9/20/2013
    Fri
    5
    9/23/2013
    Mon
    6
    9/23/2013
    Mon
    7
    9/23/2013
    Mon
    8
    9/23/2013
    Mon
    9
    9/24/2013
    Tue
    10
    9/24/2013
    Tue
    11
    9/24/2013
    Tue
    12
    9/24/2013
    Tue
    13
    9/25/2013
    Wed
    14
    9/25/2013
    Wed
    15
    9/25/2013
    Wed
    16
    9/25/2013
    Wed


    A1 = manually entered Monday thru Friday date

    This formula entered in A2 and copied down as needed:

    =WORKDAY(A$1,INT(ROWS(A$2:A2)/4))

    Note that the WORKDAY function requires the Analysis ToolPak
    add-in be installed if you're using a version of Excel prior to
    Excel 2007. If you enter the formula and get a #NAME?
    error look in Excel help for the WORKDAY function. It'll tell you
    how to fix the problem.
    Last edited by Tony Valko; 09-21-2013 at 09:41 AM.
    Biff
    Microsoft MVP Excel
    Keep It Simple Stupid

    Let's Go Pens. We Want The Cup.

  4. #4
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    563

    Re: Autofill Date with =Today() function minus weekends.

    May be like this:

    Assume A1 = Today()
    on A2
    Please Login or Register  to view this content.
    copy down A2 as long as you want
    Click (*) if you received helpful response.

    Regards,
    David

  5. #5
    Forum Guru Tony Valko's Avatar
    Join Date
    12-31-2011
    Location
    Pittsburgh
    MS-Off Ver
    2002, 2007:2013
    Posts
    18,890

    Re: Autofill Date with =Today() function minus weekends.

    Quote Originally Posted by SDCh View Post
    Assume A1 = Today()
    on A2

    =IF(COUNTIF($A$1:A1,A1)<=3,A1,IF(TEXT(A1,"dddd")="Friday",A1+3,A1+1))
    When A1 ( =TODAY() ) is a weekend date that formula returns the Sat, Sun dates for the first week.

  6. #6
    Valued Forum Contributor
    Join Date
    02-05-2013
    Location
    Jakarta, Indonesia
    MS-Off Ver
    Excel 2010
    Posts
    563

    Re: Autofill Date with =Today() function minus weekends.

    @Tony: Thanks, I'm forget if (=Today() = weekend)

    I change the formula:

    Please Login or Register  to view this content.

+ 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. formula for commit date minus completion date excluding weekends
    By jtmayo in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-07-2012, 08:42 PM
  2. [SOLVED] Macros for: If Today's Date minus other date is greater than certain amount of days Then
    By lottidotti in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 10-24-2012, 04:46 PM
  3. Today's date minus two
    By mldardy in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-12-2011, 11:51 AM
  4. Calculating Date with # of days minus weekends
    By Lazhal in forum Excel General
    Replies: 2
    Last Post: 06-10-2011, 03:28 PM
  5. Difference between todays date and a future date minus the weekends?!
    By kastle1982 in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 10-26-2006, 05:21 PM

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