I am using the following formula to check whether a change has been turned
around on time. The beginning of the formula checks whether the source column
has data in it, and if it doesn't returns a "Not Met".

However, this does not take weekends into account. I somehow need to factor
in something that will only calculate the working days. I think I should be
using 'WORKDAY', but am unsure as to how to fit this into the formula:

=IF(O37="","Not Met",IF(O37-E37LOOKUP(K37,{"High"},{5}),"Not Met", "Met"))

E is the date received; O is the date the change was dealt with. K is the
priority e.g. high, medium or low.

Anyone got any ideas please?

Thanks.

Hi

I think it should be something like

=IF(O37="","Not Met",
IF(NETWORKDAYS(E37,O37)
LOOKUP(K37,{"High","Medium","Low"},{5,10,15}),"Not Met", "Met"))

If you wanted to include Holidays, either name a range containing the
holiday dates as holidays, or use the cell references where the dates
are held, in the Networkdays part of the formula
NETWORKDAYS(E37,O37,$Z$1:$Z$10)

--
Regards

Roger Govier


"Kierano" wrote in message
...
I am using the following formula to check whether a change has been
turned
around on time. The beginning of the formula checks whether the source
column
has data in it, and if it doesn't returns a "Not Met".

However, this does not take weekends into account. I somehow need to
factor
in something that will only calculate the working days. I think I
should be
using 'WORKDAY', but am unsure as to how to fit this into the formula:

=IF(O37="","Not Met",IF(O37-E37LOOKUP(K37,{"High"},{5}),"Not Met",
"Met"))

E is the date received; O is the date the change was dealt with. K is
the
priority e.g. high, medium or low.

Anyone got any ideas please?

Thanks.

Thanks - I've tried this but no joy.

Doesn't this function need an = rather than an IF?

Rgds,
Kierano

"Roger Govier" wrote:

Hi

I think it should be something like

=IF(O37="","Not Met",
IF(NETWORKDAYS(E37,O37)
LOOKUP(K37,{"High","Medium","Low"},{5,10,15}),"Not Met", "Met"))

If you wanted to include Holidays, either name a range containing the
holiday dates as holidays, or use the cell references where the dates
are held, in the Networkdays part of the formula
NETWORKDAYS(E37,O37,$Z$1:$Z$10)

--
Regards

Roger Govier


"Kierano" wrote in message
...
I am using the following formula to check whether a change has been
turned
around on time. The beginning of the formula checks whether the source
column
has data in it, and if it doesn't returns a "Not Met".

However, this does not take weekends into account. I somehow need to
factor
in something that will only calculate the working days. I think I
should be
using 'WORKDAY', but am unsure as to how to fit this into the formula:

=IF(O37="","Not Met",IF(O37-E37LOOKUP(K37,{"High"},{5}),"Not Met",
"Met"))

E is the date received; O is the date the change was dealt with. K is
the
priority e.g. high, medium or low.

Anyone got any ideas please?

Thanks.

Hi

In what way does it not work? What do you get?

I only broke the formula to stop it wrapping in funny places with the
newsreader.
The formula would read
=IF(O37="","Not
Met",IF(NETWORKDAYS(E37,O37)LOOKUP(K37,{"High","M edium","Low"},{5,10,15}),"Not
Met", "Met"))

So there are 2 If statements.
The first, to see if there is anything in O37
The second, tests the value returned by Networkdays(E37,O37) to see if
it exceeds a value found from the Lookup, and returns Met or Not Met
accordingly.

--
Regards

Roger Govier


"Kierano" wrote in message
...
Thanks - I've tried this but no joy.

Doesn't this function need an = rather than an IF?

Rgds,
Kierano

"Roger Govier" wrote:

Hi

I think it should be something like

=IF(O37="","Not Met",
IF(NETWORKDAYS(E37,O37)
LOOKUP(K37,{"High","Medium","Low"},{5,10,15}),"Not Met", "Met"))

If you wanted to include Holidays, either name a range containing the
holiday dates as holidays, or use the cell references where the dates
are held, in the Networkdays part of the formula
NETWORKDAYS(E37,O37,$Z$1:$Z$10)

--
Regards

Roger Govier


"Kierano" wrote in message
...
I am using the following formula to check whether a change has been
turned
around on time. The beginning of the formula checks whether the
source
column
has data in it, and if it doesn't returns a "Not Met".

However, this does not take weekends into account. I somehow need
to
factor
in something that will only calculate the working days. I think I
should be
using 'WORKDAY', but am unsure as to how to fit this into the
formula:

=IF(O37="","Not Met",IF(O37-E37LOOKUP(K37,{"High"},{5}),"Not Met",
"Met"))

E is the date received; O is the date the change was dealt with. K
is
the
priority e.g. high, medium or low.

Anyone got any ideas please?

Thanks.

Thanks Roger,

In fact I had to split this formula up in order to get it to return the
correct values (I set up another column with NETWORKDAYS in order to check
the differences between days). I now have:

=IF(O5="","Not Met",IF(NETWORKDAYS(E5,O5,107)LOOKUP(K5,{"Urgent" },{2}),"Not
Met", "Met"))

....repeating the formula for each of the other statuses and replacing the
values of high, medium, low etc.

Thanks for your help - much appeciated.

Kierano

"Roger Govier" wrote:

Hi

In what way does it not work? What do you get?

I only broke the formula to stop it wrapping in funny places with the
newsreader.
The formula would read
=IF(O37="","Not
Met",IF(NETWORKDAYS(E37,O37)LOOKUP(K37,{"High","M edium","Low"},{5,10,15}),"Not
Met", "Met"))

So there are 2 If statements.
The first, to see if there is anything in O37
The second, tests the value returned by Networkdays(E37,O37) to see if
it exceeds a value found from the Lookup, and returns Met or Not Met
accordingly.

--
Regards

Roger Govier


"Kierano" wrote in message
...
Thanks - I've tried this but no joy.

Doesn't this function need an = rather than an IF?

Rgds,
Kierano

"Roger Govier" wrote:

Hi

I think it should be something like

=IF(O37="","Not Met",
IF(NETWORKDAYS(E37,O37)
LOOKUP(K37,{"High","Medium","Low"},{5,10,15}),"Not Met", "Met"))

If you wanted to include Holidays, either name a range containing the
holiday dates as holidays, or use the cell references where the dates
are held, in the Networkdays part of the formula
NETWORKDAYS(E37,O37,$Z$1:$Z$10)

--
Regards

Roger Govier


"Kierano" wrote in message
...
I am using the following formula to check whether a change has been
turned
around on time. The beginning of the formula checks whether the
source
column
has data in it, and if it doesn't returns a "Not Met".

However, this does not take weekends into account. I somehow need
to
factor
in something that will only calculate the working days. I think I
should be
using 'WORKDAY', but am unsure as to how to fit this into the
formula:

=IF(O37="","Not Met",IF(O37-E37LOOKUP(K37,{"High"},{5}),"Not Met",
"Met"))

E is the date received; O is the date the change was dealt with. K
is
the
priority e.g. high, medium or low.

Anyone got any ideas please?

Thanks.

Hi Kierano

I don't understand what the 107 is in the Networkdays part of the
formula?

Also, my mistake with the Lookup as I hadn't put the names in ascending
alphabetic order.
You can use just one formula for all conditions if you change it to

=IF(O37="","Not Met",
IF(NETWORKDAYS(E37,O37)
LOOKUP(K37,{"High","Low","Medium"},{5,15,10}),"Not Met", "Met"))

adjusting the values to suit the number of days.
Whatever names you choose for the urgency, and no matter how many you
choose, ensure that they are in ascending alphabetic order and that the
number of days matches that same order.
--
Regards

Roger Govier


"Kierano" wrote in message
...
Thanks Roger,

In fact I had to split this formula up in order to get it to return
the
correct values (I set up another column with NETWORKDAYS in order to
check
the differences between days). I now have:

=IF(O5="","Not
Met",IF(NETWORKDAYS(E5,O5,107)LOOKUP(K5,{"Urgent" },{2}),"Not
Met", "Met"))

...repeating the formula for each of the other statuses and replacing
the
values of high, medium, low etc.

Thanks for your help - much appeciated.

Kierano

"Roger Govier" wrote:

Hi

In what way does it not work? What do you get?

I only broke the formula to stop it wrapping in funny places with the
newsreader.
The formula would read
=IF(O37="","Not
Met",IF(NETWORKDAYS(E37,O37)LOOKUP(K37,{"High","M edium","Low"},{5,10,15}),"Not
Met", "Met"))

So there are 2 If statements.
The first, to see if there is anything in O37
The second, tests the value returned by Networkdays(E37,O37) to see
if
it exceeds a value found from the Lookup, and returns Met or Not Met
accordingly.

--
Regards

Roger Govier


"Kierano" wrote in message
...
Thanks - I've tried this but no joy.

Doesn't this function need an = rather than an IF?

Rgds,
Kierano

"Roger Govier" wrote:

Hi

I think it should be something like

=IF(O37="","Not Met",
IF(NETWORKDAYS(E37,O37)
LOOKUP(K37,{"High","Medium","Low"},{5,10,15}),"Not Met", "Met"))

If you wanted to include Holidays, either name a range containing
the
holiday dates as holidays, or use the cell references where the
dates
are held, in the Networkdays part of the formula
NETWORKDAYS(E37,O37,$Z$1:$Z$10)

--
Regards

Roger Govier


"Kierano" wrote in message
...
I am using the following formula to check whether a change has
been
turned
around on time. The beginning of the formula checks whether the
source
column
has data in it, and if it doesn't returns a "Not Met".

However, this does not take weekends into account. I somehow
need
to
factor
in something that will only calculate the working days. I think
I
should be
using 'WORKDAY', but am unsure as to how to fit this into the
formula:

=IF(O37="","Not Met",IF(O37-E37LOOKUP(K37,{"High"},{5}),"Not
Met",
"Met"))

E is the date received; O is the date the change was dealt with.
K
is
the
priority e.g. high, medium or low.

Anyone got any ideas please?

Thanks.

Roger,

Part 2 of your message - GREAT - IT WORKS!

Part 1 107, my mistake - I'd misunderstood an example provided by MS
Excel Help. D'oh!

Thanks again.

Kierano

"Roger Govier" wrote:

Hi Kierano

I don't understand what the 107 is in the Networkdays part of the
formula?

Also, my mistake with the Lookup as I hadn't put the names in ascending
alphabetic order.
You can use just one formula for all conditions if you change it to

=IF(O37="","Not Met",
IF(NETWORKDAYS(E37,O37)
LOOKUP(K37,{"High","Low","Medium"},{5,15,10}),"Not Met", "Met"))

adjusting the values to suit the number of days.
Whatever names you choose for the urgency, and no matter how many you
choose, ensure that they are in ascending alphabetic order and that the
number of days matches that same order.
--
Regards

Roger Govier


"Kierano" wrote in message
...
Thanks Roger,

In fact I had to split this formula up in order to get it to return
the
correct values (I set up another column with NETWORKDAYS in order to
check
the differences between days). I now have:

=IF(O5="","Not
Met",IF(NETWORKDAYS(E5,O5,107)LOOKUP(K5,{"Urgent" },{2}),"Not
Met", "Met"))

...repeating the formula for each of the other statuses and replacing
the
values of high, medium, low etc.

Thanks for your help - much appeciated.

Kierano

"Roger Govier" wrote:

Hi

In what way does it not work? What do you get?

I only broke the formula to stop it wrapping in funny places with the
newsreader.
The formula would read
=IF(O37="","Not
Met",IF(NETWORKDAYS(E37,O37)LOOKUP(K37,{"High","M edium","Low"},{5,10,15}),"Not
Met", "Met"))

So there are 2 If statements.
The first, to see if there is anything in O37
The second, tests the value returned by Networkdays(E37,O37) to see
if
it exceeds a value found from the Lookup, and returns Met or Not Met
accordingly.

--
Regards

Roger Govier


"Kierano" wrote in message
...
Thanks - I've tried this but no joy.

Doesn't this function need an = rather than an IF?

Rgds,
Kierano

"Roger Govier" wrote:

Hi

I think it should be something like

=IF(O37="","Not Met",
IF(NETWORKDAYS(E37,O37)
LOOKUP(K37,{"High","Medium","Low"},{5,10,15}),"Not Met", "Met"))

If you wanted to include Holidays, either name a range containing
the
holiday dates as holidays, or use the cell references where the
dates
are held, in the Networkdays part of the formula
NETWORKDAYS(E37,O37,$Z$1:$Z$10)

--
Regards

Roger Govier


"Kierano" wrote in message
...
I am using the following formula to check whether a change has
been
turned
around on time. The beginning of the formula checks whether the
source
column
has data in it, and if it doesn't returns a "Not Met".

However, this does not take weekends into account. I somehow
need
to
factor
in something that will only calculate the working days. I think
I
should be
using 'WORKDAY', but am unsure as to how to fit this into the
formula:

=IF(O37="","Not Met",IF(O37-E37LOOKUP(K37,{"High"},{5}),"Not
Met",
"Met"))

E is the date received; O is the date the change was dealt with.
K
is
the
priority e.g. high, medium or low.

Anyone got any ideas please?

Thanks.