Flagging tasks using Networkdays

[Ross]

Hi,
I am using a formula:
=IF(IF(D8="",TODAY(),D8)>(C8+2),"Red",IF(IF(D8="",TODAY(),D8)>=(C8+1),"Amber",""))

Column C contains dates & time received, column D date and time sent. If
date sent is empty it works on today(). We have two working days to allocate
tasks. However the formula results in ""Red" if i receive something on a
friday but do not send it till monday. Can anyone give me a clue how to add
networkdaay funtionality to this.
Many thanks,
Ross

[Niek Otten]

Hi Ross,

You need WORKDAY() functionality, not NETWORKDAYS().
=WORKDAY(a1,1)
Format as date

--
Kind regards,

Niek Otten


"Ross" <[email protected]> wrote in message news:[email protected]...
> Hi,
> I am using a formula:
> =IF(IF(D8="",TODAY(),D8)>(C8+2),"Red",IF(IF(D8="",TODAY(),D8)>=(C8+1),"Amber",""))
>
> Column C contains dates & time received, column D date and time sent. If
> date sent is empty it works on today(). We have two working days to allocate
> tasks. However the formula results in ""Red" if i receive something on a
> friday but do not send it till monday. Can anyone give me a clue how to add
> networkdaay funtionality to this.
> Many thanks,
> Ross
>

[Bob Phillips]

=VLOOKUP(INT(IF(D8="",TODAY(),D8)-(C8+2+(WEEKDAY(C8)=6)*2+(WEEKDAY(C8)=5)*2)
),{0,"";1,"Amber";2,"Red"},2)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Ross" <[email protected]> wrote in message
news:[email protected]...
> Hi,
> I am using a formula:
>
=IF(IF(D8="",TODAY(),D8)>(C8+2),"Red",IF(IF(D8="",TODAY(),D8)>=(C8+1),"Amber
",""))
>
> Column C contains dates & time received, column D date and time sent. If
> date sent is empty it works on today(). We have two working days to
allocate
> tasks. However the formula results in ""Red" if i receive something on a
> friday but do not send it till monday. Can anyone give me a clue how to
add
> networkdaay funtionality to this.
> Many thanks,
> Ross
>

[daddylonglegs]

Try this

=IF(C8="","",LOOKUP(NETWORKDAYS(C8,IF(D8,D8,TODAY())),{0,2,4;"","Amber","Red"}))

[Ross]

Thank you, this is very helpful.
Ross

"daddylonglegs" wrote:

>
> Try this
>
> =IF(C8="","",LOOKUP(NETWORKDAYS(C8,IF(D8,D8,TODAY())),{0,2,4;"","Amber","Red"}))
>
>
> --
> daddylonglegs
> ------------------------------------------------------------------------
> daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
> View this thread: http://www.excelforum.com/showthread...hreadid=521681
>
>