2 Cool Fishing Forum banner

1 - 20 of 24 Posts

·
I'll keep my gun...You keep the change
Joined
·
10,507 Posts
Discussion Starter · #1 ·
I used to have a spreadsheet made by a secretary years ago that I used for safety tracking.......I dont have it anymore and need some advice on how to make it again

Works like this



Say an employee's confined space training goes out on March 1st of 2010
In the spreadsheet beside his name and under Confined Space training goes this date.....3.01.10
When the date is 90 days away...the cell turns blue
when the date is 30 days from past due the cell turns yellow
when the date has expired or on the date of expiration the cell turns red

Its is or was a great spreadsheet for tracking safety training and I would like to have another one but am/are too freaking dumb to know how to make one........

Anyone have the knowledge on how to make this work

TS:confused:
 

·
Registered
Joined
·
4,664 Posts
Conditional formatting, you need to have the date in the spreadsheet and then do conditional formatting based on that, may have to do a macro, let me know if you need help with either of those two things, the macro would be a more difficult way to approach.
 

·
Registered
Joined
·
4,664 Posts
I made a formula that you can use, PM me your email adress, all you would then have to do is set up conditional formatting based on the number the cell returns, the formula looks like this...

=IF(AND((B3-D2)>60,(B3-D2)<90),1,IF(AND((B3-D2) >30, (B3-D2)<60),2,IF((B3-D2)<30,3,0)))

B3 is the "B" column where you put the employee "end" date, D2 is the cell that has the formula =today() which always returns the days date when you open the spreadsheet and it will update everything, PM me if you want this.
 

·
I'll keep my gun...You keep the change
Joined
·
10,507 Posts
Discussion Starter · #7 ·
I looked for condition formatting...and it said please step away form the keyboard....lol
 

·
Registered
Joined
·
1,862 Posts
TS, send your sheet over to POC and let him do it. You two get on the phone real quick and you can tell POC what you want it to do. Probably save a couple hours! lol
 

·
Registered
Joined
·
4,664 Posts
Ya, you didn't know i was smart like that did ya danny!!! sent you an email, see if that works, if it doesn't holler at me and i can check it out. I'm leaving the office for the day, so i'd have to look at it in the morning.
 

·
I'm a millionaire in a .02 opinion world
Joined
·
2,569 Posts
No actually I expect you delicate office types to know all that kinda crud. Things are gonna change when B&P gets that transfer to downtown. New sheriff in town. It's good to be the King. B&P
 

·
Kickin' hippies' a**es and raisin' h*ll!
Joined
·
5,369 Posts
What do you do with it?????
You stick that long bit of stuff (the nested IF>THEN statements) into a cell. The equation will give you a number, either 1, 2, or 3. Each number corresponds to how far out their retraining date is.

Then use the conditional formatting to change the color based on the value. Red if 1, Blue if 2, yellow if 3...and so forth.
 

·
Registered
Joined
·
416 Posts
An easier way would be to do this.

Highlight the column you want to color.
Goto Conditional Formatting -> Highlight Cells -> Between
In the first cell put =today()
In the second cell put =today()+30
Choose the color you want to highlight

Samething for the 60 days
First Cell put =today()+31
First Cell put =today()+60

Suggest you add one to highlight red when the date is pass due also.

Loyd
 

·
Registered
Joined
·
416 Posts
An easier way would be to do this.

Highlight the column you want to color.
Goto Conditional Formatting -> Highlight Cells -> Between
In the first cell put =today()
In the second cell put =today()+30
Choose the color you want to highlight

Samething for the 60 days
First Cell put =today()+31
First Cell put =today()+60

Suggest you add one to highlight red when the date is pass due also.

Loyd
Actually I tried this and only works for today. Nevermind.
 
1 - 20 of 24 Posts
Top