Need some help with Excel

Ford Freak · 01-15-2008, 01:36 PM

I am working on a spreadsheet for tracking inspections on equipment at work. Some stuff needs to be done every 180 days and other stuff needs to be done every two years. I have made blocks to put the dates that they are due. I'd like to know if it is possible to have that block change from one color to another one month before the inspection is due and then to another color two weeks before it is due.

**k0NG0** · 01-15-2008, 02:00 PM

Maybe this will help: Microsoft Excel Date & Time Formulas Tips and Tricks

It appears TODAY() is the function to call to get the current date. And DATEDIF() can get you the number of years, months, days between two dates.

So DATEDIF(A1, TODAY(), "m") would give the number of months between today and the date in cell A1 (the first date to the function must be later than the second, else it spits out an error). Use "d" instead of "m" for # of days.

As for changing colors based on that, you'd need a function that checks the cell for the time difference and sets the color based on that. If that's doable with only Excel formulas or if it needs some sort of VB thingie, I don't know.

01-15-2008, 01:36 PM	#1
Ford Freak warranty voider Join Date: Oct 2001 Location: Valdosta, GA Posts: 3,823	Need some help with Excel I am working on a spreadsheet for tracking inspections on equipment at work. Some stuff needs to be done every 180 days and other stuff needs to be done every two years. I have made blocks to put the dates that they are due. I'd like to know if it is possible to have that block change from one color to another one month before the inspection is due and then to another color two weeks before it is due. __________________
(Offline)

01-15-2008, 02:00 PM	#2
k0NG0 Remembering TQ Join Date: Mar 2001 Location: Sweden Posts: 13,623	Re: Need some help with Excel Maybe this will help: Microsoft Excel Date & Time Formulas Tips and Tricks It appears TODAY() is the function to call to get the current date. And DATEDIF() can get you the number of years, months, days between two dates. So DATEDIF(A1, TODAY(), "m") would give the number of months between today and the date in cell A1 (the first date to the function must be later than the second, else it spits out an error). Use "d" instead of "m" for # of days. As for changing colors based on that, you'd need a function that checks the cell for the time difference and sets the color based on that. If that's doable with only Excel formulas or if it needs some sort of VB thingie, I don't know. __________________ Use Firefox - "the one that blocks all the schmutz" Feeling multicore elation? Remember this correlation: Amdahl's Law.
(Offline)

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode