Change dtes into charts

[garykcc]

I work in a small tool hire shop and we have dificulty in tracking the dates that machines are hired for. I know a little about excel and I can create a list of dates for each machine but others in the shop are not computer literate.

Can I create a macro that can be run by anybody to enter the machine number and dates to he hired. And create a chart of some kind to show when an item is out on hire.

The problem is that notes are left for me to input and these notes sometimes get lost.

Thanks for any help that you can offer me.

Gary

[Derek]

Hi Gary

Something like this might be part of what you are after.
In A1 type "TOOL No"
List all your tool numbers in Column A (from A2 downwards)
In B1 type "RETURN DATE"
in C1 type the formula =TODAY()
in D1 type the formula = C1+1. Grab the fill handle and scroll the formula right to column AG
Select C2:AG2 and enter this conditional formatting:
From the toolbar select Format, Conditional Formatting
Change "Cell Value Is" to "Formula Is"
enter this formula =$B2>C$1
Select Format, Patterns and select green
Click "Add" for a second condition
enter this formula =$B2=C$1
Select Format, Patterns and select red
Click Ok
GO BACK AND ENSURE THAT QUOTE MARKS HAVE NOT AUTOMATICALLY ENTERED ROUND THE FORMULAS

Select C2:AG2 again, then continue to select downwards all the rows to the row of your last tool number.
From the toolbar select Format, Conditional Formatting and click OK
Whilst still selected, format all these cells with a border (so they are boxes)

This now gives you columns of 31 days always commencing with today's date
If you enter the return hire date in Column B against the tool number, days hired out show green,
return day shows red, available for hire is uncolored.

Protect the sheet so your staff can only enter the hire return date in column B

Freeze Frame row 1 so that the dates are visible when your staff scroll down

hope this gives you some ideas
regards
Derek


[ This Message was edited by: Derek on 2002-03-22 04:54 ]

[garykcc]

Thank you ever so much Derek.

I will try it out.

Gary