+ Reply to Thread
Results 1 to 11 of 11

Need Help Tracking Last 30 Days and Excluding Weekends

Hybrid View

  1. 05-02-2013, 11:06 AM #1
    AlphaSkidz
    AlphaSkidz is offline
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Need Help Tracking Last 30 Days and Excluding Weekends

    I need help tracking the last 30 days, but also excluding weekends. In cell A1 I currently have the formula =Today()-30 so that everyday it will automatically change to 30 days ago. In cell A2 I have =A1+1, in cell A3 I have =A2+1, and this goes on until Cell A30. The problem I run into is that by doing this it will include weekend dates which I do not need. The data from this is charted, and if the weekends are included then it will show 0 on the chart, and affect the true trendline. Any help with this would be appreciated.
    Reply With QuoteRegister To Reply
  2. 05-02-2013, 11:18 AM #2
    Melvinrobb
    Melvinrobb is offline
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Need Help Tracking Last 30 Days and Excluding Weekends

    Do you want the weekdays over the last 30 days (around 23 days) or the last 30 weekdays?
    Please click the * icon below if I have helped.
    Reply With QuoteRegister To Reply
  3. 05-02-2013, 11:20 AM #3
    AlphaSkidz
    AlphaSkidz is offline
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Need Help Tracking Last 30 Days and Excluding Weekends

    Last 30 weekdays
    Reply With QuoteRegister To Reply
  4. 05-02-2013, 11:22 AM #4
    Melvinrobb
    Melvinrobb is offline
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Need Help Tracking Last 30 Days and Excluding Weekends

    Put the following formula in A1:
    Formula: copy to clipboard 
    =WORKDAY(TODAY(),-30)

    Put the following formula in A2 and drag down:
    Formula: copy to clipboard 
    =WORKDAY(A1,1)


    Change the "-30" to -29 if you want the final day to be TODAY.
    Reply With QuoteRegister To Reply
  5. 05-02-2013, 11:47 AM #5
    AlphaSkidz
    AlphaSkidz is offline
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Need Help Tracking Last 30 Days and Excluding Weekends

    @Melvinrobb, As the date changes won't it start including the weekends? For instance, when the system clock reads 5/4/2013 won't that formula show weekends?
    Reply With QuoteRegister To Reply
  6. 05-02-2013, 11:39 AM #6
    bebo021999
    bebo021999 is offline
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    9,645

    Re: Need Help Tracking Last 30 Days and Excluding Weekends

    Try in A1:
    =WORKDAY(TODAY(),ROW(A1)-30)
    or
    =LARGE(IF(WEEKDAY(ROW(INDIRECT(TODAY()&":"&TODAY()-50)),2)>5,"",ROW(INDIRECT(TODAY()&":"&TODAY()-50))),31-ROW(A1))
    Confirmed with Ctrl-shift-enter
    Drag down
    Quang PT
    Reply With QuoteRegister To Reply
  7. 05-02-2013, 11:51 AM #7
    Melvinrobb
    Melvinrobb is offline
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Need Help Tracking Last 30 Days and Excluding Weekends

    I don't know why it should. Change the formula in A1 to:
    Formula: copy to clipboard 
    =WORKDAY(C2,-30)
    and put different dates in c2 and see if it gives the results you want.
    Reply With QuoteRegister To Reply
  8. 05-02-2013, 11:56 AM #8
    AlphaSkidz
    AlphaSkidz is offline
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Need Help Tracking Last 30 Days and Excluding Weekends

    works perfect thanks for the help.
    Reply With QuoteRegister To Reply
  9. 05-02-2013, 01:42 PM #9
    AlphaSkidz
    AlphaSkidz is offline
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Need Help Tracking Last 30 Days and Excluding Weekends

    @Melvinrobb if I wanted to track the last 30 days instead of last 30 workdays, while also excluding weekends, how would I do that?
    Reply With QuoteRegister To Reply
  10. 05-02-2013, 01:43 PM #10
    Melvinrobb
    Melvinrobb is offline
    Valued Forum Contributor Melvinrobb's Avatar
    Join Date
    06-19-2012
    Location
    Manitoba, Canada
    MS-Off Ver
    Excel 2013
    Posts
    1,128

    Re: Need Help Tracking Last 30 Days and Excluding Weekends

    Use the same formula you used in A1 previously:
    Formula: copy to clipboard 
    =TODAY()-30

    and this formula in A2:
    Formula: copy to clipboard 
    =WORKDAY(A1,1)
    Reply With QuoteRegister To Reply
  11. 05-02-2013, 02:21 PM #11
    AlphaSkidz
    AlphaSkidz is offline
    Registered User
    Join Date
    04-30-2013
    Location
    Arkansas
    MS-Off Ver
    Excel 2013
    Posts
    96

    Re: Need Help Tracking Last 30 Days and Excluding Weekends

    Works perfect, thanks for your help.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1