+ Reply to Thread
Results 1 to 9 of 9

Working days left in the month compared to previous months

  1. #1
    Registered User
    Join Date
    09-26-2005
    Posts
    34

    Data sum with working days left in the month compared to previous months

    Hello all-

    I have data recorded on a daily level that I would like to summarize by month however there is a catch. I would like to compare the previous months data to the current month by the number of working days left.

    For example todays date is 10/22/05 and there are 6 working days left in the month. I want to look at all previous months for the year and see what our totals were with 6 working days remaining.

    The end result will give me two totals for each month where I can look at it and go "In January, with 6 workdays left we had already sold 10 widgets, and in those remaining 6 workdays we sold 5 more widgets, compared to this month where we've sold 18 widgets with 6 workdays remaining"

    Using the Today() function I can get the beginning date of the current month, end date of the current month, total working days, working days completed, and working days remaining, (counting the current day as completed) those are all not a problem.

    The date is obviously different for each month, September 22 is the date in September when there were only 6 workdays left (Counting the current date as completed), August 23rd is the date in August when there were only 6 workdays left. etc etc.

    My data source is very simple. It is ascending daily dates in column A with the data in column B.

    It's hard to translate my thoughts into a post but I hope I've drawn a good picture for you. I look forward to your responses.
    Last edited by qwopzxnm; 10-22-2005 at 05:30 PM.

  2. #2
    Roland
    Guest

    RE: Working days left in the month compared to previous months

    I believe that you'll find the NETWORKDAYS function to be exactly what you
    need.



    "qwopzxnm" wrote:

    >
    > Hello all-
    >
    > I have data recorded on a daily level that I would like to summarize by
    > month however there is a catch. I would like to compare the previous
    > months data to the current month by the number of working days left.
    >
    > For example todays date is 10/22/05 and there are 6 working days left
    > in the month. I want to look at all previous months for the year and
    > see what our totals were with 6 working days remaining.
    >
    > Using the Today() function I can get the beginning date of the current
    > month, end date of the current month, total working days, working days
    > completed, and working days remaining, those are all not a problem. My
    > problem is establishing a date in the previous months that will
    > corralate the number of work days remaining in that month, to equal to
    > the number of working days remaining in this month.
    >
    > The date for that is obviously different for each month, September 23
    > is the date in September when there were only 6 workdays left, August
    > 24th is the date in August when there were only 6 workdays left. etc
    > etc.
    >
    >
    > My data source is simple it is ascending daily dates in column A with
    > the data in column B.
    >
    > It's hard to translate my thoughts into a post but I hope I've drawn a
    > good picture for you. I look forward to your responses.
    >
    >
    > --
    > qwopzxnm
    > ------------------------------------------------------------------------
    > qwopzxnm's Profile: http://www.excelforum.com/member.php...o&userid=27557
    > View this thread: http://www.excelforum.com/showthread...hreadid=478495
    >
    >


  3. #3
    Registered User
    Join Date
    09-26-2005
    Posts
    34
    Roland thanks for your response-

    I understand the NETWORKDAYS funtion but I need to find a way to get a start date for that each month; if that's the best way to solve this.

    Or better yet, how could I use the NETWORKDAYS function to accomplish my goal??

  4. #4
    Roger Govier
    Guest

    Re: Working days left in the month compared to previous months

    Hi

    Try
    =WORKDAY(DATE(YEAR(A1),MONTH(A1),1),NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),EOMONTH(A1,0))-6)
    This ignores holidays.
    If you ant to include holidays, then either name a range containing your
    holiday dates called, Holidays, or give the range where the dates are held
    in the following formula

    =WORKDAY(DATE(YEAR(A23),MONTH(A23),1),NETWORKDAYS(DATE(YEAR(A23),MONTH(A23),1),EOMONTH(A23,0),holidays)-6)



    Regards

    Roger Govier


    qwopzxnm wrote:
    > Roland thanks for your response-
    >
    > I understand the NETWORKDAYS funtion but I need to find a way to get a
    > start date for that each month; if that's the best way to solve this.
    >
    > Or better yet, how could I use the NETWORKDAYS function to accomplish
    > my goal??
    >
    >


  5. #5
    Ron Rosenfeld
    Guest

    Re: Working days left in the month compared to previous months

    On Sat, 22 Oct 2005 16:04:29 -0500, qwopzxnm
    <[email protected]> wrote:

    >
    >Hello all-
    >
    >I have data recorded on a daily level that I would like to summarize by
    >month however there is a catch. I would like to compare the previous
    >months data to the current month by the number of working days left.
    >
    >For example todays date is 10/22/05 and there are 6 working days left
    >in the month. I want to look at all previous months for the year and
    >see what our totals were with 6 working days remaining.
    >
    >Using the Today() function I can get the beginning date of the current
    >month, end date of the current month, total working days, working days
    >completed, and working days remaining, those are all not a problem. My
    >problem is establishing a date in the previous months that will
    >corralate the number of work days remaining in that month, to equal to
    >the number of working days remaining in this month.
    >
    >The date for that is obviously different for each month, September 23
    >is the date in September when there were only 6 workdays left, August
    >24th is the date in August when there were only 6 workdays left. etc
    >etc.
    >
    >
    >My data source is simple it is ascending daily dates in column A with
    >the data in column B.
    >
    >It's hard to translate my thoughts into a post but I hope I've drawn a
    >good picture for you. I look forward to your responses.


    Use the WORKDAY function.

    For example, you have already computed the working days left in this month.

    To get the equivalent date in the previous month, back up to the FIRST day of
    this month, and subtract the requisite number of working days.

    =WORKDAY(TODAY()-DAY(TODAY())+1, -6)

    or

    =workday(TODAY()-DAY(TODAY())+1,
    -networkdays(TODAY(),DATE(YEAR(
    TODAY()),MONTH(TODAY())+1,0)))


    --ron

  6. #6
    Registered User
    Join Date
    09-26-2005
    Posts
    34
    Ron-

    Thank you for your reply as well. I thought of using that approach but if you figure out the remaining work days in the current month and then subtract those from the last day of the previous month, it does not give you the same result.

    For instance if there are 6 work days left in this month, and the last day of the previous month was a Monday, then your solution would go back 6 days, 2 of which are Saturday and Sunday. This would give me a date in the previous month that had 4 work days left, and not 6.

    This problem seemed like it has an easy solution when I first started but the more you get into it, the harder it becomes to get the result

  7. #7
    Ron Rosenfeld
    Guest

    Re: Working days left in the month compared to previous months

    On Sun, 23 Oct 2005 21:56:06 -0500, qwopzxnm
    <[email protected]> wrote:

    >
    >Ron-
    >
    >Thank you for your reply as well. I thought of using that approach but
    >if you figure out the remaining work days in the current month and then
    >subtract those from the last day of the previous month, it does not
    >give you the same result.
    >
    >For instance if there are 6 work days left in this month, and the last
    >day of the previous month was a Monday, then your solution would go
    >back 6 days, 2 of which are Saturday and Sunday. This would give me a
    >date in the previous month that had 4 work days left, and not 6.
    >
    >This problem seemed like it has an easy solution when I first started
    >but the more you get into it, the harder it becomes to get the result
    >


    Sure it does.

    If you are NOT getting the proper result, you are NOT using the method I
    suggested. Perhaps you are subtracting DAYS instead of WORKDAYS as I posted?

    Look at HELP for the WORKDAY function to understand better.

    Then post your method which is not working, with the formulas rather than just
    the concept, so we can suggest the appropriate changes.


    --ron

  8. #8
    Roger Govier
    Guest

    Re: Working days left in the month compared to previous months

    Hi

    In case you didn't see my posting which I sent at 12:25 on 23/10/05, I
    repeat it below. I tested it pretty thoroughly, and believe it does answer
    your problem.

    =WORKDAY(DATE(YEAR(A1),MONTH(A1),1),NETWORKDAYS(DATE(YEAR(A1),MONTH(A1),1),EOMONTH(A1,0))-6)

    This ignores holidays.
    If you ant to include holidays, then either name a range containing your
    holiday dates called, Holidays, or give the range where the dates are held
    in the following formula

    =WORKDAY(DATE(YEAR(A23),MONTH(A23),1),NETWORKDAYS(DATE(YEAR(A23),MONTH(A23),1),EOMONTH(A23,0),holidays)-6)

    Regards

    Roger Govier


    qwopzxnm wrote:
    > Ron-
    >
    > Thank you for your reply as well. I thought of using that approach but
    > if you figure out the remaining work days in the current month and then
    > subtract those from the last day of the previous month, it does not
    > give you the same result.
    >
    > For instance if there are 6 work days left in this month, and the last
    > day of the previous month was a Monday, then your solution would go
    > back 6 days, 2 of which are Saturday and Sunday. This would give me a
    > date in the previous month that had 4 work days left, and not 6.
    >
    > This problem seemed like it has an easy solution when I first started
    > but the more you get into it, the harder it becomes to get the result
    >
    >
    >


  9. #9
    Registered User
    Join Date
    09-26-2005
    Posts
    34
    Thanks again for both of your responses, helped me solve it.

+ 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