G
Guest
I set up a worksheet to notify myself of monthly anniversary dates for a list of random starting dates. The formula works very well for the initial date and 5 subsequent anniversary dates but after that (if the formula is expanded) an error message appears stating that the formula contains an error.
Specifics: The list of starting dates is in column B. Column headings (in row 2) are the dates, in sequence, of every day for 12 months (i.e. D2 = 07/01/04, E2 = 07/02/04, F2 = 07/03/04...., etc.). The formula is set up so that all cells in a given row default to "-" unless the cell is in a column that is a monthly anniversary of the starting date. For example, the starting date in row 5 is 08/09/04. All cells (moving to the right) in row 5 show a "-" until the AQ5 cell which is in the column with the heading (in AQ2) of 08/09/04. This cell displays a "1" signifying the first 'flagged' date (meaning it is equal to the starting date). Continuing to the right, the cells again all show a "-" until the BV5 cell which is in the column with the heading (in BV2) of 09/09/04. This cell displays a "2" indicating it is the 2nd 'flagged' date (one month past the starting date). This continues with a “3†in CZ5 (heading 10/09/04) and a “4’ in EE5 (heading 11/09/04), etc. A portion of the formula (copied from cell AQ5) for this calculation is: “=IF(AQ$2=$B5,1,IF(AQ$2=DATE(YEAR($B5),MONTH($B5)+1,DAY($B5)),2,IF(AQ$2=DATE(YEAR($B5),MONTH($B5)+2,DAY($B5)),3,IF…â€. The repeating section of this formula is “IF(AQ$2=DATE(YEAR($B5),MONTH($B5)+N,DAY($B5)),n,†where N and n each increase by 1 with each subsequent section.
Questions: Can you tell me what the error may be? Or, is this problem due to the limitations of Excel? If so, can you offer a way to get around those limitations? (I would like to be able to go out for a full 12 months if possible.) Thanks for your help!
Specifics: The list of starting dates is in column B. Column headings (in row 2) are the dates, in sequence, of every day for 12 months (i.e. D2 = 07/01/04, E2 = 07/02/04, F2 = 07/03/04...., etc.). The formula is set up so that all cells in a given row default to "-" unless the cell is in a column that is a monthly anniversary of the starting date. For example, the starting date in row 5 is 08/09/04. All cells (moving to the right) in row 5 show a "-" until the AQ5 cell which is in the column with the heading (in AQ2) of 08/09/04. This cell displays a "1" signifying the first 'flagged' date (meaning it is equal to the starting date). Continuing to the right, the cells again all show a "-" until the BV5 cell which is in the column with the heading (in BV2) of 09/09/04. This cell displays a "2" indicating it is the 2nd 'flagged' date (one month past the starting date). This continues with a “3†in CZ5 (heading 10/09/04) and a “4’ in EE5 (heading 11/09/04), etc. A portion of the formula (copied from cell AQ5) for this calculation is: “=IF(AQ$2=$B5,1,IF(AQ$2=DATE(YEAR($B5),MONTH($B5)+1,DAY($B5)),2,IF(AQ$2=DATE(YEAR($B5),MONTH($B5)+2,DAY($B5)),3,IF…â€. The repeating section of this formula is “IF(AQ$2=DATE(YEAR($B5),MONTH($B5)+N,DAY($B5)),n,†where N and n each increase by 1 with each subsequent section.
Questions: Can you tell me what the error may be? Or, is this problem due to the limitations of Excel? If so, can you offer a way to get around those limitations? (I would like to be able to go out for a full 12 months if possible.) Thanks for your help!