Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
qwopzxnm
 
Posts: n/a
Default 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.

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

  #2   Report Post  
Roland
 
Posts: n/a
Default 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   Report Post  
qwopzxnm
 
Posts: n/a
Default Working days left in the month compared to previous months


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

  #4   Report Post  
Roger Govier
 
Posts: n/a
Default Working days left in the month compared to previous months

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


  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default Working days left in the month compared to previous months

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


  #6   Report Post  
qwopzxnm
 
Posts: n/a
Default Working days left in the month compared to previous months


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

  #7   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default Working days left in the month compared to previous months

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
  #8   Report Post  
Roger Govier
 
Posts: n/a
Default 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(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
:)


  #9   Report Post  
qwopzxnm
 
Posts: n/a
Default Working days left in the month compared to previous months


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

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I create a schedule from a list of dates ? Gavin Morris Charts and Charting in Excel 2 October 28th 10 04:08 PM
automatically update chart plotting current month and previous 6 Jane Charts and Charting in Excel 1 September 1st 05 10:19 PM
HELP with this function Jay Excel Worksheet Functions 7 May 24th 05 06:45 PM
Generating business days in a calendar month, EXCLUDING holidays jacob Excel Worksheet Functions 0 April 12th 05 04:57 AM
get the latest day of the previous month Laurent M Excel Discussion (Misc queries) 2 January 26th 05 04:22 PM


All times are GMT +1. The time now is 12:22 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"