Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Confused beginner
Ok, I know there are a lot of posts about this subject but I am a complete beginner and need very simple step by step instructions. I have a spreadsheet that needs a formula (or something) to calculate the number of working days between say the date entered in A1 and todays date not including saturdays, sundays and english bank holidays. thats the simple part. Then, if the difference is 0-3 days i want the K1 cell to be green in colour. If its 3-7 days I want it orange and if its more than 7 days i want it red. Then once a date has been entered into J1 I want it to stay the colour it was at the time the date in J1 was entered. If that makes sense. Can anyone help? Thanks in advance. Sarah -- sarahog ------------------------------------------------------------------------ sarahog's Profile: http://www.excelforum.com/member.php...o&userid=28569 View this thread: http://www.excelforum.com/showthread...hreadid=482294 |
#2
|
|||
|
|||
Confused beginner
On Fri, 4 Nov 2005 12:47:56 -0600, sarahog
wrote: Ok, I know there are a lot of posts about this subject but I am a complete beginner and need very simple step by step instructions. I have a spreadsheet that needs a formula (or something) to calculate the number of working days between say the date entered in A1 and todays date not including saturdays, sundays and english bank holidays. thats the simple part. Then, if the difference is 0-3 days i want the K1 cell to be green in colour. If its 3-7 days I want it orange and if its more than 7 days i want it red. Then once a date has been entered into J1 I want it to stay the colour it was at the time the date in J1 was entered. If that makes sense. Can anyone help? Thanks in advance. Sarah It's not immediately clear what the date in J1 is for. The difference between today's date and the A1 date is simply =now()-A1-1 and formatted as a number. You can use Conditional formatting to colour the cell where you enter the formula. HTH __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
#3
|
|||
|
|||
Confused beginner
Ok thanks for that, the date in A1 would be the date something is sent out. The date in J1 would be the date it is eventually sent back. I'll try what you said and see how I get on. Thanks a lot -- sarahog ------------------------------------------------------------------------ sarahog's Profile: http://www.excelforum.com/member.php...o&userid=28569 View this thread: http://www.excelforum.com/showthread...hreadid=482294 |
#4
|
|||
|
|||
Confused beginner
The simplest and most straightforward way for you to do this is make
the K1 cell contain the elapsed days, excluding holidays etc. There is a formula for this. In K1 put this formula: =IF(ISNUMBER(A1),NETWORKDAYS(A1,IF(ISNUMBER(J1),J1 ,TODAY()),Holidays),"") Holidays is the name of a list of the Bank Holidays and other holidays you want to exclude. You need to set this up as a named range, and put the dates of the holidays in it. Now use conditional formatting to colour K1 - the outstanding days. Choose Format/Conditional Formatting. In the Condition 1 drop down box cheese "Formula". Enter this formula in the box to the right =AND(ISNUMBER(K1),K17) Click Format and then click the Patterns tab. Choose a red colour. Click OK. Now click the Add button. This will produce another condition. Follow the same procedure and enter this formula =AND(ISNUMBER(K1),K12) assuming 2 is your cut-off (your message was ambiguous). Now click Format and choose an Orange colour. Click OK Now click the Add button and enter the following comndition =ISNUMBER(K1) and proceed to choose a green colour as above. You could avoid the use of K1 and put all these formulas into the conditional formatting conditions but that would be a chore and is prone to error. |
#5
|
|||
|
|||
Confused beginner
I forgot to add that NETWORKDAYS implicitly excludes Saturdays and
Sundays. You do not need to include them in your list of holidays. Look up NETWORKDAYS in Excel help for more. HTH DOR |
#6
|
|||
|
|||
Confused beginner
Sorry, I overlooked the fact that NETWORKDAYS counts the start day AND
the end date, so, If you do not want to count the start date, you need to subtract an additional 1. This would make the formula in K1 =IF(ISNUMBER(A1),NETWORKDAYS(A1,IF(ISNUMBER(J1),J1 ,TODAY()),Holidays)-1,"") |
#7
|
|||
|
|||
Confused beginner
Thanks so much for all your help. I'll give it a try tonight and let you know how I get on. Thanks so much guys :-) -- sarahog ------------------------------------------------------------------------ sarahog's Profile: http://www.excelforum.com/member.php...o&userid=28569 View this thread: http://www.excelforum.com/showthread...hreadid=482294 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Royally Confused!! | Excel Discussion (Misc queries) | |||
Confused about arrays and ranges in functions | Excel Worksheet Functions | |||
check off, look up forms - VERY confused | Excel Discussion (Misc queries) | |||
PV(), FV() confused by sign expected | Excel Worksheet Functions | |||
Frank, Sumproduct, it works but I am confused | Excel Worksheet Functions |