Non standard holidays across different states

  • Thread starter Thread starter Rajkumar1
  • Start date Start date
R

Rajkumar1

Hi Guys,

My issue is I need to run the performance reports for our company
operates across 5 cities (Australia)

The performance of an activity is the difference between "actual" an
"agreed" (in days)

While using the NETWORKING DAYS, I am able to input one set o
holidays: holidays that are common across all states like Dec 25 or Ja
1

The issue I face is how to account for the holidays that are UNIQUE?..
the holidays unique to each state? like...

1. Like Victoria (state in Australia) did not get a holiday for Anza
Day while all other states got
2. and the Labour day is different amongst the states as well

Can I request for some assistance please

Thanks and regards,
Ra
 
Rajkumar1 said:
*Hi Guys,

My issue is I need to run the performance reports for our company
operates across 5 cities (Australia)

The performance of an activity is the difference between "actual" an
"agreed" (in days)

While using the NETWORKING DAYS, I am able to input one set o
holidays: holidays that are common across all states like Dec 25 o
Jan 1

The issue I face is how to account for the holidays that ar
UNIQUE?... the holidays unique to each state? like...

1. Like Victoria (state in Australia) did not get a holiday for Anza
Day while all other states got
2. and the Labour day is different amongst the states as well

Can I request for some assistance please

Thanks and regards,
Raj
Click to expand...
 
I don't really understand the question, can't you just use different holiday
ranges for each state?
 
Hi Rajkumar1!

Two things occur to me here:

First you'll need to use different holiday ranges for different States. You
might use separate formulas for this or nest a formula in the Holidays
argument that selects the appropriate range.

Secondly, you are going to have to account for replacement holidays in your
list of holidays. For example, this year Anzac Day fell on a Sunday and in
NSW where a public Holiday falls on a Saturday or Sunday the following
Monday becomes a Public Holiday. Also Christmas Day and Boxing Day fall on
Saturday and Sunday this year with New Year Day 2005 also falling on a
Saturday. Replacements will be awarded in all three cases in NSW but I'm not
sure about other States. You can get over this problem with formulas in your
holiday range that use the WEEKDAY function. (The formula for Boxing Day
will be unusual because its replacement will be the Tuesday this year).

--
--
Regards
Norman Harker MVP (Excel)
Sydney, Australia
(e-mail address removed)
Excel and Word Function Lists (Classifications, Syntax and Arguments)
available free to good homes.
 
Thanks Norman,

Let me explain the issue a bit more...

I have one spreadsheet with several rows of data…
showing actual and contract days, each row may correspond to
particular state (they are not sorted)

The reporting process is as follows (so it can be done by anyone)

1. Run a crystal report to extract the actual performance data (Weekl
process)
2. Cut and paste (Dump) this raw data on to the spreadsheet (which
designed) at the end
3. … this process is repeated each week
4. the person then drags the formulae across the new rows (that wa
recently added)
5. refresh the Pivot table
6. And he has the updated report – table and graph

The advantage is no excel knowledge required to maintain this reportin
process

Coming to your suggestion,
We will need to sort the raw data to be added in terms of STATES, an
apply the right formulae so that the HOLIDAY array (within NETWORKDAYS
corresponds to the STATE in question

This requires some knowledge of excel, I was trying to avoid it an
make the maintenance as simple as possible

Any suggestions? To overcome this issue, yet keeping it simple!?
Do you thing any of the logical function could work?

Kind regards,
Raj


the rows
I was looking for some sora
 
Thanks Peo,

Let me explain the issue a bit more...

I have one spreadsheet with several rows of data…
showing actual and contract days, each row may correspond to
particular state (they are not sorted)

The reporting process is as follows (so it can be done by anyone)

1. Run a crystal report to extract the actual performance data (Weekl
process)
2. Cut and paste (Dump) this raw data on to the spreadsheet (which
designed) at the end
3. … this process is repeated each week
4. the person then drags the formulae across the new rows (that wa
recently added)
5. refresh the Pivot table
6. And he has the updated report – table and graph

The advantage is no excel knowledge required to maintain this reportin
process

Coming to your suggestion,
We will need to sort the raw data to be added in terms of STATES, an
apply the right formulae so that the HOLIDAY array (within NETWORKDAYS
corresponds to the STATE in question

This requires some knowledge of excel, I was trying to avoid it an
make the maintenance as simple as possible

Any suggestions? To overcome this issue, yet keeping it simple!?
Do you thing any of the logical function could work?

Kind regards,
Raj
 
Let me explain the issue a bit more...

I have one spreadsheet with several rows of data…
showing actual and contract days, each row may correspond to a
particular state (they are not sorted)
Click to expand...

If I understand what you are doing, each row would need to have an entry
defining the state to which it applies. You could then use that state to
define the holiday range to be used.

For example, assume the state is in Column A.

Assume that beginning at column J, you have columns which represent the holiday
range for each state, with a header row (row 1) that has the names of each
state.

Also assume that the Start Date is in column B, and the End Date is in Column
C.

The formula:

=NETWORKDAYS(B7,C7,OFFSET($J$1,1,MATCH(A7,$J$1:$N$1,0)-1,10,1))


would look at the state name in column A and match it with the appropriate
holiday range. In the formula, the '10' within the OFFSET function represents
a number that is equal to or greater than the maximum number of holidays in any
state.



--ron
 
Hi Rajkumar1!

If you have the State data in a separate cell, then the approach might
be:

1. Name the ranges containing holidays for the States e.g.
NSWHolidays, VICHolidays etc.
2. Then you can use the NETWORKDAYS formula using INDIRECT to to
construct the address of the holiday range:

=NETWORKDAYS(A1,B1,INDIRECT(C1&"Holidays"))

Issues on replacement holidays can be addressed in the holiday lists
for each State.
 
Back
Top