Flagging tasks using Networkdays
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 |
Flagging tasks using Networkdays
Hi Ross,
You need WORKDAY() functionality, not NETWORKDAYS(). =WORKDAY(a1,1) Format as date -- Kind regards, Niek Otten "Ross" wrote in message ... 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 |
Flagging tasks using Networkdays
=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" wrote in message ... 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 |
Flagging tasks using Networkdays
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 |
Flagging tasks using Networkdays
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 |
All times are GMT +1. The time now is 09:28 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com