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

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

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


--
qwopzxnm
------------------------------------------------------------------------
qwopzxnm's Profile: http://www.excelforum.com/member.php...o&userid=27557
View this thread: http://www.excelforum.com/showthread...hreadid=478495

Hi

Try
=WORKDAY(DATE(YEAR(A1),MONTH(A1),1),NETWORKDAYS(DA TE(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),holida ys)-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??

On Sat, 22 Oct 2005 16:04:29 -0500, 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.

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

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
:)


--
qwopzxnm
------------------------------------------------------------------------
qwopzxnm's Profile: http://www.excelforum.com/member.php...o&userid=27557
View this thread: http://www.excelforum.com/showthread...hreadid=478495

On Sun, 23 Oct 2005 21:56:06 -0500, 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
:)

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

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(DA TE(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),holida ys)-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
:)

Thanks again for both of your responses, helped me solve it.


--
qwopzxnm
------------------------------------------------------------------------
qwopzxnm's Profile: http://www.excelforum.com/member.php...o&userid=27557
View this thread: http://www.excelforum.com/showthread...hreadid=478495