help with formula in excel

i have a formula problem with excel that i need help with

okay i have a list of people that have 4 course dates next to there names in seperate coloums. as those dates get closer to the current date i need there name and date to move to the top of the list so i can get refresher courses done. currently i am going through and moving them to the top in singles the ones that are over due turn red, when they are 2 weeks out from been over due they turn yellow and at 4 weeks out they turn dark green and anything else they are just white if you could give me a formula or a place where i can find one that would be great

or even if it hits a certian colour it moves them to the top of the list in the colour order of red yellow green white

oh and this is in excel 2003

thanks kris

February 15th, 2013 4:53am

i need 4 different coloums set up so that when the date in respective cells get to 2 weeks and 4 weeks from the current date they change colour 2 weeks out needs to highlight the cell yellow and 4 weeks out from current date it highlights cell dark green and when they are older than the current date they go red

any help is welcome

thanks kris

February 15th, 2013 5:53am

Hi Kris,

I made an small example for you. I write some vba code to do that, just check to see whether this is what you want:

And the macro code is here:

Sub color_date()
Dim UserRange As Range
Dim day_number As Integer
Dim today_date As Date

On Error Resume Next
Set UserRange = Application.InputBox( _
Prompt:="Select the range of dates", _
Title:=Title, _
Type:=8) 'Range selection
On Error GoTo 0

today_date = Date

For Each cell In UserRange
day_number = DateDiff("d", cell.Value, today_date)
Select Case day_number
Case Is > 0
With Selection.Interior
.Color = 255
End With
Case between - 14 To 0
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.ThemeColor = xlThemeColorDark1
End With
Case between - 28 To -14
With Selection.Interior
.Color = 65535
End With
End Select
Next

End Sub

After run the macro, it will ask you to select the range of dates, just select the cells which contains the dates then click OK.

February 18th, 2013 10:32am

so will this work running off todays date and the date as the year goes on or do i have to enter the current date everytime i open this spread sheet

February 19th, 2013 12:10am

No, you do not need to. It will get today's date in the code.
March 5th, 2013 1:00pm

Dear Jaynet Zhang,

I hope if someone here can help me with a formula where I want to get how many days it is due. I want to subtract due dates with the current day and keeps on updating or incrementing with 1 on the next day. And, using Excel 2013

Thanks if you can help me with this in advance :)

Regards,
Flava