Given a cell with a date value, how would one format a cell with style A if it is 30 days before an upcoming anniversary date, format a cell with style B if it is within 30 days after an anniversary date, else not format at all?
The best way to achieve that, create the formula to test in the spreadsheet, when it returns a true/false value right, then create the conditional format to apply desired style or use the STYLE() help
@mariosv I end up with the formula =IF(EDATE(P11,(DATEDIF(P11,TODAY(),"y")+1)*12)-DATE(YEAR(EDATE(P11,(DATEDIF(P11,TODAY(),"y")+1)*12)),MONTH(TODAY()),DAY(TODAY()))<30,T(STYLE("due-soon-style"))&"due soon",T(STYLE("not-due-soon-style"))&"not due soon") which seems to work. However, the style is not applied to any cells in my even rows because I have even rows conditionally formatted (ISEVEN(ROW(A1)) for the purpose of alternating row colors and it applies to all cells.
-
It seems conditional formatting takes precedence over setting
STYLE()on a cell–if it’s not possible to override this, what’s a workaround? -
Similarly, I want to set override the conditional rule for alternating row colors with a rule that conditionally formats a cell in a particular column if it has a certain value. It’s not possible to set precedence for this condition over the condition rule for alternating row colors because the rules act on different sets of cells. Any solutions?
Thanks.
You cannot “overrule” conditional formatting. You need to incorporate your anniversary condition as an additional condition before the condition for the alternating row colors. If you need that for different sets of cells, you need to split your “alternating-row-colors-ranges” as well.
Anniversaries or the like handled by formulas based on standard functions tend to get complicated and to make it difficult to check them for correctness under any circumstances.
A calculation step by step and remembering intermediary results can be more clearly structured. Therefore I took the opportunity to sketch a user fuction (in Basic) doing it the second way.
You may check the code below against your solutions. Don’t omit tests with anniversaries of leapdays within leapyears and within “normal” years as well.
Function nearestAnniversaries(pOrigDate, pThenDate, Optional pWhich As String)
Dim dates(1)
nearestAnniversaries = dates
If pThenDate<=pOrigDate Then REM No "backward anniversaries".
Exit Function
End If
oY = Year(pOrigDate) : oM = Month(pOrigDate) : oD = Day(pOrigDate)
oLeap = (oM=2) AND (oD=29)
tY = Year(pThenDate) : tM = Month(pThenDate) : tD = Day(pThenDate)
dates(0) = DateSerial(tY, oM, oD - IIf(oLeap, 1, 0)) + IIf(oLeap, 1, 0)
diff0 = dates(0) - pThenDate
If diff0>0 Then
dates(1) = dates(0)
dates(0) = DateSerial(tY-1, oM, oD - IIf(oLeap, 1, 0)) + IIf(oLeap, 1, 0)
Else
dates(1) = DateSerial(tY+1, oM, oD - IIf(oLeap, 1, 0)) + IIf(oLeap, 1, 0)
End If
If NOT IsMissing(pWhich) Then
Select Case pWhich
Case "-", "1"
nearestAnniversaries = dates(0)
Case "+", "2"
nearestAnniversaries =dates(1)
Case Else
nearestAnniversaries = "#param!"
End Select
End If
End Function
“Manual:”
The original date for which anniversaries are to be calculated is pOrigDate.
The function always first calculates as seen from pThenDate
== the most recent anniversary past or just reachedAND
== the next anniversary to come.
If pWhich is omitted, the function returns both these results as an array with two values, the earlier date with the lower index. Called from Calc under array-evaluation the two results are output to two adjacent cells in a row.
Using pWhich you can restrict the output to the earlier date placing “-” or 1 there. With “+” or 2 you get the later date.
The NumberFormat you want to apply to the results you need to set for the cells. The function cannot force any format without a convertsion to text.