Working days left in the month compared to previous months

[qwopzxnm]

Hello all-

I have data recorded on a daily level that I would like to summarize b
month however there is a catch. I would like to compare the previou
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 lef
in the month. I want to look at all previous months for the year an
see what our totals were with 6 working days remaining.

Using the Today() function I can get the beginning date of the curren
month, end date of the current month, total working days, working day
completed, and working days remaining, those are all not a problem. M
problem is establishing a date in the previous months that wil
corralate the number of work days remaining in that month, to equal t
the number of working days remaining in this month.

The date for that is obviously different for each month, September 2
is the date in September when there were only 6 workdays left, Augus
24th is the date in August when there were only 6 workdays left. et
etc.


My data source is simple it is ascending daily dates in column A wit
the data in column B.

It's hard to translate my thoughts into a post but I hope I've drawn
good picture for you. I look forward to your responses

 

[Guest]

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

 

[qwopzxnm]

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

 

[Roger Govier]

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

 

[Ron Rosenfeld]

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

 

[qwopzxnm]

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

 

[Ron Rosenfeld]

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

 

[Roger Govier]

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]

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