Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Box666
 
Posts: n/a
Default Calculation with Working day of the year

Using the "standard formula" I am able to work out that (say)the 21st
June is working day 117 for this year using
=NETWORKDAYS($U$4;B17;$V$3:$AE$5)- where U4 is 01/01/2005, B17 is
21/06/2005 and V3 to AE5 is a list of non working days.

I now need to know what working day 117 was last year, is it possible
to start with working day no. and then work out the date?

  #2   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default Calculation with Working day of the year

On 10 Nov 2005 07:01:34 -0800, "Box666" wrote:

Using the "standard formula" I am able to work out that (say)the 21st
June is working day 117 for this year using
=NETWORKDAYS($U$4;B17;$V$3:$AE$5)- where U4 is 01/01/2005, B17 is
21/06/2005 and V3 to AE5 is a list of non working days.

I now need to know what working day 117 was last year, is it possible
to start with working day no. and then work out the date?


Look at the WORKDAY formula:

=WORKDAY(DATE(2004,1,1),117,holidays)

Be sure to include the holiday (non-working) day dates in the holidays range.


--ron
  #3   Report Post  
Niek Otten
 
Posts: n/a
Default Calculation with Working day of the year

Use the WORKDAY() function; WORKDAY(A1,117,holidays) where A1 is 1-1-2004
and holidays contain last years's non working days

--
Kind regards,

Niek Otten

"Box666" wrote in message
oups.com...
Using the "standard formula" I am able to work out that (say)the 21st
June is working day 117 for this year using
=NETWORKDAYS($U$4;B17;$V$3:$AE$5)- where U4 is 01/01/2005, B17 is
21/06/2005 and V3 to AE5 is a list of non working days.

I now need to know what working day 117 was last year, is it possible
to start with working day no. and then work out the date?



  #4   Report Post  
Box666
 
Posts: n/a
Default Calculation with Working day of the year

Ron,
Thank you spot on, I do not seem to have the logical thinking to
work these things backwards.. but when I see the answer i realise how
"obvious" it is.

Thanks

Bob

  #5   Report Post  
Ron Rosenfeld
 
Posts: n/a
Default Calculation with Working day of the year

On 10 Nov 2005 08:27:34 -0800, "Box666" wrote:

Ron,
Thank you spot on, I do not seem to have the logical thinking to
work these things backwards.. but when I see the answer i realise how
"obvious" it is.

Thanks

Bob


You're welcome. Glad to help. Sometimes it's interesting to find a function
that I did not know existed.


--ron
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
Automatic calculation is not working susang Excel Discussion (Misc queries) 4 November 4th 05 04:51 PM
Commission Calculation nospaminlich Excel Worksheet Functions 8 November 1st 05 03:47 PM
Working days left in the month compared to previous months qwopzxnm Excel Worksheet Functions 8 October 24th 05 08:00 PM
Is the IRR calculation based on cash flows at beginning of year? sammad Excel Worksheet Functions 1 September 6th 05 05:15 PM
Calculation Setting in Excel Stuart Bisset Excel Discussion (Misc queries) 0 June 17th 05 09:54 AM


All times are GMT +1. The time now is 07:04 PM.

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"