# Working days left in the month compared to previous months

1. ## 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.

2. ## 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. 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. ## 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. ## Re: Working days left in the month compared to previous months

On Sat, 22 Oct 2005 16:04:29 -0500, qwopzxnm
<qwopzxnm.1xbnmb_1130015115.8651@excelforum-nospam.com> 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. 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. ## Re: Working days left in the month compared to previous months

On Sun, 23 Oct 2005 21:56:06 -0500, qwopzxnm
<qwopzxnm.1xdyya_1130123115.1666@excelforum-nospam.com> 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. ## 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. Thanks again for both of your responses, helped me solve it.

#### Thread Information

##### Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### 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