Confused beginner

[sarahog]

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

 

[Richard Buttrey]

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
__________________________

 

[sarahog]

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

 

[DOR]

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),K1>7)

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),K1>2) 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.

 

[DOR]

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

 

[DOR]

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,"")

 

[sarahog]

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