Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Function for Anniversary Dates
I have a spreadsheet that tracks our employee's significant anniversary dates
(year 1, 5, 7, 10, 20, 25, etc.). I need a formula that will highlight those employees names and the significant anniversary for the the next 45 days (e.g., any day that I open the spreadsheet, any employee with an upcoming significant anniversary will have their name highlighted and the date of the corresponding anniversary hightlighted.) Here's how my spreadsheet is set up: Data begins on row 2 & Columns begin with A A2 = Month/Day B2 = hidden column containing unrelated data C2 = Date of Hire D2 = Employee Name E2 -J2 = Hidden columns containing unrelated data K2 = 1st year anniversary date (calculates from DOH +1) L2 = 5th year anniversary date (calculates from DOH + 5) M2 = 7th year anniversary date (etc.) N2 = 10th year anniversary date (etc.) O2 = 15th " P2 = 20th " Q2 = 25th " R2 = 30th " S2 = 35th " T2 = 40th " My conditional formatting formula looks like this: =AND(TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))<15,TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))0)) and is yielding the wrong formatting. Instead of showing me that this coming Tuesday is John Doe's 7th year anniversary by highlighting only the date for his 7th year (which would be next Tuesday's date), all the anniversaries for John Doe are highlighted -including those in the past. I found the formula on this site and modified the cells to correspond but it's still off and I don't know why. I'm having a hard time dissecting the formula to see what each portion is asking for so I can't figure out how to fix it. Help! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Function for Anniversary Dates
Obviously subtract different numbers of years for the different
anniversaries. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Should you not be subtracting the 7 years? Try: =AND(C2-DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))<=15,C2-DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))=0) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "blucajun" wrote in message ... I have a spreadsheet that tracks our employee's significant anniversary dates (year 1, 5, 7, 10, 20, 25, etc.). I need a formula that will highlight those employees names and the significant anniversary for the the next 45 days (e.g., any day that I open the spreadsheet, any employee with an upcoming significant anniversary will have their name highlighted and the date of the corresponding anniversary hightlighted.) Here's how my spreadsheet is set up: Data begins on row 2 & Columns begin with A A2 = Month/Day B2 = hidden column containing unrelated data C2 = Date of Hire D2 = Employee Name E2 -J2 = Hidden columns containing unrelated data K2 = 1st year anniversary date (calculates from DOH +1) L2 = 5th year anniversary date (calculates from DOH + 5) M2 = 7th year anniversary date (etc.) N2 = 10th year anniversary date (etc.) O2 = 15th " P2 = 20th " Q2 = 25th " R2 = 30th " S2 = 35th " T2 = 40th " My conditional formatting formula looks like this: =AND(TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))<15,TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))0)) and is yielding the wrong formatting. Instead of showing me that this coming Tuesday is John Doe's 7th year anniversary by highlighting only the date for his 7th year (which would be next Tuesday's date), all the anniversaries for John Doe are highlighted -including those in the past. I found the formula on this site and modified the cells to correspond but it's still off and I don't know why. I'm having a hard time dissecting the formula to see what each portion is asking for so I can't figure out how to fix it. Help! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Function for Anniversary Dates
Now I'm really confused. I'm no beginner for Excel, but I just don't
understand what the formula is doing and why I should be subtracting. Anyway, I put the formula you gave into the conditional formatting field, but it didn't produce any of my formatting for those upcoming anniversaries. I'm still lost on the subtracting 7 part, but I'm not a math wiz so... Actually, this formula is making me feel really dumb. Ugh. "Sandy Mann" wrote: Obviously subtract different numbers of years for the different anniversaries. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Should you not be subtracting the 7 years? Try: =AND(C2-DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))<=15,C2-DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))=0) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "blucajun" wrote in message ... I have a spreadsheet that tracks our employee's significant anniversary dates (year 1, 5, 7, 10, 20, 25, etc.). I need a formula that will highlight those employees names and the significant anniversary for the the next 45 days (e.g., any day that I open the spreadsheet, any employee with an upcoming significant anniversary will have their name highlighted and the date of the corresponding anniversary hightlighted.) Here's how my spreadsheet is set up: Data begins on row 2 & Columns begin with A A2 = Month/Day B2 = hidden column containing unrelated data C2 = Date of Hire D2 = Employee Name E2 -J2 = Hidden columns containing unrelated data K2 = 1st year anniversary date (calculates from DOH +1) L2 = 5th year anniversary date (calculates from DOH + 5) M2 = 7th year anniversary date (etc.) N2 = 10th year anniversary date (etc.) O2 = 15th " P2 = 20th " Q2 = 25th " R2 = 30th " S2 = 35th " T2 = 40th " My conditional formatting formula looks like this: =AND(TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))<15,TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))0)) and is yielding the wrong formatting. Instead of showing me that this coming Tuesday is John Doe's 7th year anniversary by highlighting only the date for his 7th year (which would be next Tuesday's date), all the anniversaries for John Doe are highlighted -including those in the past. I found the formula on this site and modified the cells to correspond but it's still off and I don't know why. I'm having a hard time dissecting the formula to see what each portion is asking for so I can't figure out how to fix it. Help! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Function for Anniversary Dates
Well the date in your formula:
DATE(YEAR(TODAY()),MONTH(C3),DAY(C3)) returns the date of the anniversary *this year* so it will match *every* year but you want it to match only the 7th anniversary year. What I did therefore was to subtract the number of years on the anniversary - in this case 7 - and compare it to the Hire date so that only one formula would return TRUE. In other words in my formula: DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY())) returns the date of this day in the *Hire year* and this is subtracted form the Hire date. If the Hire date was 26 June 2001 then any date (that my formula calculates) from 12 June 2001 to 26 June 2001 will be within the 14 days that you want the highlighting to occur. As to why it does not work for you I cannot say because it works for me. If the above does not help then send me an email by changing the address in my signature as it says to replace the part after the @ so that I can get your address, (DON'T post it here or you will be targeted by the Spam bots) and I will send you a demo spreadsheet. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "blucajun" wrote in message ... Now I'm really confused. I'm no beginner for Excel, but I just don't understand what the formula is doing and why I should be subtracting. Anyway, I put the formula you gave into the conditional formatting field, but it didn't produce any of my formatting for those upcoming anniversaries. I'm still lost on the subtracting 7 part, but I'm not a math wiz so... Actually, this formula is making me feel really dumb. Ugh. "Sandy Mann" wrote: Obviously subtract different numbers of years for the different anniversaries. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Should you not be subtracting the 7 years? Try: =AND(C2-DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))<=15,C2-DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))=0) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "blucajun" wrote in message ... I have a spreadsheet that tracks our employee's significant anniversary dates (year 1, 5, 7, 10, 20, 25, etc.). I need a formula that will highlight those employees names and the significant anniversary for the the next 45 days (e.g., any day that I open the spreadsheet, any employee with an upcoming significant anniversary will have their name highlighted and the date of the corresponding anniversary hightlighted.) Here's how my spreadsheet is set up: Data begins on row 2 & Columns begin with A A2 = Month/Day B2 = hidden column containing unrelated data C2 = Date of Hire D2 = Employee Name E2 -J2 = Hidden columns containing unrelated data K2 = 1st year anniversary date (calculates from DOH +1) L2 = 5th year anniversary date (calculates from DOH + 5) M2 = 7th year anniversary date (etc.) N2 = 10th year anniversary date (etc.) O2 = 15th " P2 = 20th " Q2 = 25th " R2 = 30th " S2 = 35th " T2 = 40th " My conditional formatting formula looks like this: =AND(TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))<15,TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))0)) and is yielding the wrong formatting. Instead of showing me that this coming Tuesday is John Doe's 7th year anniversary by highlighting only the date for his 7th year (which would be next Tuesday's date), all the anniversaries for John Doe are highlighted -including those in the past. I found the formula on this site and modified the cells to correspond but it's still off and I don't know why. I'm having a hard time dissecting the formula to see what each portion is asking for so I can't figure out how to fix it. Help! |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Conditional Formatting Function for Anniversary Dates
Well I don't know where my reply giving an explanation went to because it is
in my Sent box but I don't see ity in the NG - damned OE. Of course it would have been much better if I had written the formula the other way around and *added* 7 years to the Hire date because it uses fewer function calls: =AND(DATE(YEAR(C2)+7,MONTH(C2),DAY(C2))-TODAY()<15,DATE(YEAR(C2)+7,MONTH(C2),DAY(C2))-TODAY()=0) Explantion, (of the above formula) The date in your formula: DATE(YEAR(TODAY()),MONTH(C3),DAY(C3)) returns the date of the anniversary *this year* so it will match *every* year, (year 1, 5, 7, 10, 20, 25, etc.), but you want it to match only the 7th anniversary year. What I have done is to add the number of years to the Hire date - in this case 7 - and compare it to the today's date so that only one formula would return TRUE. In other words in my formula: DATE(YEAR(C2)+7,MONTH(C2),DAY(C2)) returns the date of the anniversary of the *Hire year* seven years on then today's date is subtracted from this date to give the number of days until the hire date. If the Hire date was 26 June 2001 then any date (that my formula calculates) from 12 June 2008 to 26 June 2008 will be within the 14 days that you want the highlighting to occur. As to why it does not work for you I cannot say because it works for me unles it is the fact that in your formula you are referencing C3 noit C2 although you quote C2 as being the Hire date. If the above does not help then send me an email by changing the address in my signature as it says to replace the part after the @ so that I can get your address, (DON'T post it here or you will be targeted by the Spam bots) and I will send you a demo spreadsheet. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "blucajun" wrote in message ... Now I'm really confused. I'm no beginner for Excel, but I just don't understand what the formula is doing and why I should be subtracting. Anyway, I put the formula you gave into the conditional formatting field, but it didn't produce any of my formatting for those upcoming anniversaries. I'm still lost on the subtracting 7 part, but I'm not a math wiz so... Actually, this formula is making me feel really dumb. Ugh. "Sandy Mann" wrote: Obviously subtract different numbers of years for the different anniversaries. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Sandy Mann" wrote in message ... Should you not be subtracting the 7 years? Try: =AND(C2-DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))<=15,C2-DATE(YEAR(TODAY())-7,MONTH(TODAY()),DAY(TODAY()))=0) -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "blucajun" wrote in message ... I have a spreadsheet that tracks our employee's significant anniversary dates (year 1, 5, 7, 10, 20, 25, etc.). I need a formula that will highlight those employees names and the significant anniversary for the the next 45 days (e.g., any day that I open the spreadsheet, any employee with an upcoming significant anniversary will have their name highlighted and the date of the corresponding anniversary hightlighted.) Here's how my spreadsheet is set up: Data begins on row 2 & Columns begin with A A2 = Month/Day B2 = hidden column containing unrelated data C2 = Date of Hire D2 = Employee Name E2 -J2 = Hidden columns containing unrelated data K2 = 1st year anniversary date (calculates from DOH +1) L2 = 5th year anniversary date (calculates from DOH + 5) M2 = 7th year anniversary date (etc.) N2 = 10th year anniversary date (etc.) O2 = 15th " P2 = 20th " Q2 = 25th " R2 = 30th " S2 = 35th " T2 = 40th " My conditional formatting formula looks like this: =AND(TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))<15,TODAY()-DATE(YEAR(TODAY()),MONTH(C3),DAY(C3))0)) and is yielding the wrong formatting. Instead of showing me that this coming Tuesday is John Doe's 7th year anniversary by highlighting only the date for his 7th year (which would be next Tuesday's date), all the anniversaries for John Doe are highlighted -including those in the past. I found the formula on this site and modified the cells to correspond but it's still off and I don't know why. I'm having a hard time dissecting the formula to see what each portion is asking for so I can't figure out how to fix it. Help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formatting with Dates | Excel Worksheet Functions | |||
anniversary dates | Excel Discussion (Misc queries) | |||
Date range selection? Anniversary dates | Excel Worksheet Functions | |||
Function Dates in Conditional Formatting | Excel Worksheet Functions | |||
recurring anniversary dates | Excel Discussion (Misc queries) |