Ask Your Question

Check if date is within 30 days before/after anniversary

asked 2020-04-10 20:03:07 +0100

zf gravatar image

updated 2020-08-07 13:28:29 +0100

Alex Kemp gravatar image

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?

edit retag flag offensive close merge delete



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

m.a.riosv gravatar imagem.a.riosv ( 2020-04-10 22:29:23 +0100 )edit

@m.a.riosv 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 ...

zf gravatar imagezf ( 2020-04-11 08:07:07 +0100 )edit

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.

Opaque gravatar imageOpaque ( 2020-04-11 12:05:39 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-04-12 00:02:28 +0100

Lupp gravatar image

updated 2020-04-12 00:10:07 +0100

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)
  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

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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-04-10 20:03:07 +0100

Seen: 74 times

Last updated: Apr 12 '20