Za trzy tygodnie, za trzy miesiące, mija termin... formatowanie warunkowe.

Może to proste ale nie wiedziałem, co wprowadzić jako formułę do formatowania warunkowego:

Za trzy miesiące:
=H5<=(EDATE(TODAY();3))

Za trzy tygodnie:
=H5<=(TODAY()+(7*3))

A co, jeśli data będzie powtarzać się cyklicznie? Np. co roku należy przeprowadzić kontrolę pojazdu etc.

Poniższa formuła podświetli wszystkie zbliżające się daty, na 3 tygodnie przed upłynięciem terminu.

=AND(TODAY()>=DATE(YEAR(TODAY());MONTH(K2);DAY(K2))-21;TODAY()<=DATE(YEAR(TODAY());MONTH(K2);DAY(K2)))

A gdy data jest w zakresie od dziś do trzech tygodni po terminie?

=AND(TODAY()<=DATE(YEAR(TODAY());MONTH(K2);DAY(K2))+21;TODAY()>DATE(YEAR(TODAY());MONTH(K2);DAY(K2)))

Edit: co jednak zrobić gdy kończy się rok a badana data jest na początku stycznia? Należałoby przyjąć, że  =IF(YEAR(TODAY()+21)=YEAR(TODAY())+1;YEAR(TODAY())+1;YEAR(TODAY()))

Po dodaniu powyższego obliczenia do formuły podświetlającej daty na 3 tygodnie przed upłynięciem terminu otrzymujemy takie monstrum (w Excelu można formułę podzielić na linie za pomocą Alt+Enter:

Czy 3 tygodnie i mniej przed terminem?


=AND(
TODAY()>=DATE(
(IF(YEAR(TODAY()+21)=YEAR(TODAY())+1;YEAR(TODAY())+1;YEAR(TODAY())));
MONTH(K2);DAY(K2))-21;
TODAY()<=DATE(
IF(YEAR(TODAY()+21)=YEAR(TODAY())+1;YEAR(TODAY())+1;YEAR(TODAY()));
MONTH(K2);DAY(K2)))

Czy do 3 tygodni po terminie?

=AND(TODAY()<=DATE(
IF(YEAR(TODAY()+21)=YEAR(TODAY())+1;YEAR(TODAY())+1;YEAR(TODAY()));
MONTH(K2);DAY(K2))+21;
TODAY()>DATE(
IF(YEAR(TODAY()+21)=YEAR(TODAY())+1;YEAR(TODAY())+1;YEAR(TODAY()));
MONTH(K2);DAY(K2)))

---

Co dalej (Edit 2015-06-06):
1. Chciałbym funkcję uprościć, zamienić na funkcję użytkownika (UDF) i umożliwić odhaczanie wykonanych napraw, np. przez porównanie do daty ostatnio wykonanej naprawy. Przy okazji nauczyłem się jak zwrócić #N/A :

Function ServiceDue(purchaseDate As Date, daysInAdv As Integer, _
Optional serviceDoneDate As Date) As Variant

    Application.Volatile
    
    Dim advDateBase As Date
    advDateBase = DateSerial(IIf(Year(Date) + daysInAdv = Year(Date) + 1, Year(Date) + 1, Year(Date)), _
    Month(purchaseDate), Day(purchaseDate))

If serviceDoneDate = Null Or Year(serviceDoneDate) <> Year(advDateBase) Then

    If Date > advDateBase - daysInAdv And Date <= advDateBase Then
        ServiceDue = advDateBase - Date
    ElseIf Date <= (advDateBase + daysInAdv) And Date > advDateBase Then
        ServiceDue = advDateBase - Date     'the same but I expect different action.
    Else
        ServiceDue = CVErr(xlErrNA)         'note how to return #NA
    End If
    
Else
    ServiceDue = CVErr(xlErrNA)
End If
    
    
End Function

2.Trzeba jeszcze posortować po kolorach. Jeszcze jeden problem. Ktoś doda kolumnę i makro przestaje działać albo sortowana jest niewłaściwa kolumna. Chciałbym odnosić się nie do zakresu ale do konkretnej kolumny, po nazwie. Czyli zamiast: .Add(Range("K1:K1") wolałbym bezpieczniej .Add(Range("1:1").Find(What:="Date of instalation/training", MatchCase:=False). Sortuję najpierw jeden potem drugi kolor tak by zaznaczone kolorem zielonym daty zbliżające się wystąpiły po datach dla których termin upłynął. W przypadku pierwszego koloru odnoszę się do nazwy kolumny, w przypadku drugiego do zakresu (dla porównania):

Private Sub Worksheet_Activate()

Dim lstobj As Object: Set lstobj = ActiveWorkbook.Worksheets("1").ListObjects("Table1")
    With lstobj.Sort
            .SortFields.Clear
            .SortFields.Add(Range("1:1").Find(What:="Date of instalation/training", MatchCase:=False), xlSortOnCellColor, xlAscending, , xlSortNormal) _
            .SortOnValue.Color = RGB(192, 0, 0)
            .SortFields.Add(Range("K1:K1"), xlSortOnCellColor, xlAscending, , xlSortNormal) _
            .SortOnValue.Color = RGB(0, 92, 42)
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
    End With
End Sub