Creating a timesheet for a charity project - forumla help :)

[acather96]

Hello all,
Let me explain the situation. I volunteer for a charity who do elderly and disabled people's gardens for free and clear snow from their drive in winter in the local community, also helping the unemployed back into work. Recently, we have started to expand and the admin for the group is getting quite difficult, so I took it upon myself to create an Excel timesheet that we could use to keep on top of things. I've got the basics set up - name, address etc., the only 'complicated' function so far is an EDATE formula which tells us when a job next needs to be done; attached is a picture of the workbook so fartaskforce workbook.png. However, as many of the volunteers are quite elderly, ease of use is a primary concern and one thing that volunteers said would be good is a colour-coding system. Let me explain:

In an ideal world:
1) If we have over two weeks till the job needs to be done, the horizontal row containing that job would turn green.
2) If we have under two weeks till the job needs to be done, the horizontal row containing that job would turn yellow.
3) If the job is overdue, the horizontal row containing that job would turn red.

I've got a 'current date' function at the top, and have tried and failed for a couple of hours to use conditional formatting to make this work. It's probably something really simple (and it's just I'm a bit thick :P), but I and many others would be immensely grateful if someone could explain how this would be possible. If you could post the solution you will be helping possibly hundreds of people in the local community, and have my eternal gratitude. Thanks very much in advance

[Pete_UK]

It would be helpful if you could post the workbook, then we can put the solution(s) in there for you. Just "sanitise" it a bit beforehand, i.e. remove the names and addresses and save the file with a different name. Then, when you get your solution it will be relatively easy for you to copy/paste the names and addresses back again.

Pete

[acather96]

Thanks for the quick reply -attached is the workbook with bogus names. Really appreciate this

[Pete_UK]

So, you want to compare the dates in column G with B1 and set up the colours for columns A to G for each row?

Pete

[acather96]

Yes, comparing the current date with the to be done by date, and then colouring in that job's row. E.g. on the example I posted, the first job would be green, the second yellow and third red. Thanks very much

[Pete_UK]

Okay, the attached file does this for you. I've also used the Format Painter to apply the conditional formatting to the blank rows down to about row 30.

Let me know if you need any further help in applying it to your real data.

Hope this helps.

Pete

[acather96]

Thanks so much Pete, me and everyone at the Taskforce appreciates this so much. God bless

[acather96]

One question, come to think of it - how can I view the formula if I wish to change it at a later date?

[Pete_UK]

When setting this up, you select all the cells that you want this to apply to first, eg from A5 to G20, then click on Conditional Formatting and then New Rule. From that list you would select "Use a formula ... " (at the bottom of the list), and the formula I have used is:

=AND($G5<>"",$G5<$B$1)

and used this for Red. I've also used:

=AND($G5<>"",$G5-14<$B$1)

for gold/yellow, and this one:

=AND($G5<>"",$G5-14>$B$1)

for green. The first term in the AND is needed so that you can apply the CF to rows where column G is empty.

If you want to look at these or amend them then just select any cell in that range, click on Conditional Formatting, then Manage Rules (i.e. existing ones), then select the one you want and click on Edit Rule.

Hope this helps.

Pete

EDIT: By the way, your charity seems just the thing for me - pity Bedfordshire is so far away !!

[acather96]

Thanks once again, you're a genius

[Pete_UK]

Well, thanks for feeding back. If you feel that your problem is now solved, please mark the Thread as such (the FAQ describes how). Also, you can pass on thanks directly to any contributor who you think has helped you by clicking on the "star" icon in the bottom left corner of any post that has helped.

Pete