Results 1 to 7 of 7

Thread: Date Formula

  1. #1
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136

    Smile Unanswered: Date Formula

    I have four cells.

    Cell A1 that contains a date the user enters manually.

    Cell A2 needs to contain the date for the previous work day from the date entered into cell A1.

    Cell A3 needs to contain the number of work days that have passed from the first work day of the month thru the date in cell A2.

    Cell A4 needs to contain the total number of work days in a specific month.

    Our work days are Monday thru Saturday.

    So for the month of March, cell A4 would have the number 27.

    If 3/5/07 is entered in cell A1, cell A2 would have 3/3/07, and cell A3 would have the number 3.

    If 3/8/07 is entered in cell A1, cell A2 would have 3/7/07, and cell A3 would have the number 6.

    Do I need another cell that contains the month and year so the formula in cell A4 knows which month to count the working days for or could it reference the date in cell A2 to know which month and year to use?

    Thanks in advance for your time.
    Thanks,
    Zenaida


    - If you've never made a mistake, you've never made anything.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    A2: DateAdd function (this can only be used in VBA code)
    Syntax: DateAdd ( interval, number, date )
    Example: DateAdd (d, -1, <YourCell>)

    A3: DateDiff function (also only available in VBA)
    Syntax: DateDiff( interval, date1, date2 )

    A4: Again - DateDiff

    HTH
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I can't think of a way to do this without VB, either. Take a look here
    http://www.dbforums.com/showthread.php?t=1605962
    I posted some code in there that does just what your looking for.
    You'll just have to make some changes for a six day week, and adapt it
    to put the data into your cells.
    Inspiration Through Fermentation

  4. #4
    Join Date
    Mar 2006
    Posts
    163
    You might want to look at all the date functions available in Excel and the those from the Analysis ToolPak.

  5. #5
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I came across the formula below. Does anyone see any reason why this might cause me problems?

    What I'm hoping this formula does is: count number of work days for a 6 day work week Monday thru Saturday. Cell A1 contains the first day of the month, cell A2 contains the last work date.

    =NETWORKDAYS(A1,A2)+(INT((((A2+6)-(A1+6))+MOD(7-(A1+6),7))/7)+1)

    So if cell A1 contains 3/1/07 and cell A2 contains 3/17/07, the formula would return the number 15

    Thanks,
    Zenaida
    ________________________
    Using Excel 2000

  6. #6
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I tested the above formula with different dates and it didn't work. Couldn't figure out what the formula was doing. It would give me different numbers with the same end date??

    Anyway, I've been testing the formula below. If anyone thinks I might have a problem with it, I would greatly appreciate any comments.

    =NETWORKDAYS(A1,A2)+INT(A2/7)-INT(A1/7)+INT(WEEKDAY(A1)/7)

    _________________
    Using Excel 2000


    Thanks,
    Zenaida

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I suggest you start by splitting the formula so you can work out what each bit is doing.

    =NETWORKDAYS(A1,A2)
    =INT(A2/7)-INT(A1/7)
    =INT(WEEKDAY(A1)/7)
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •