Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight birthdays
Using conditional formatting how would I highlight birthdays coming up
within the next 14 days. I can't use < today()+14 since birth year is in the past. I don't mind using VBA if this helps |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight birthdays
Hi
There may be a simpler formula, but the following seems to work =AND(MONTH(A1)=MONTH(TODAY())+(DAY(A1)-DAY(TODAY())<0),DAY(A1)-DAY(TODAY())<=14) -- Regards Roger Govier jocker wrote: Using conditional formatting how would I highlight birthdays coming up within the next 14 days. I can't use < today()+14 since birth year is in the past. I don't mind using VBA if this helps |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight birthdays
Hi
With birth date in cell A2 =AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))=T ODAY()) You also can try =AND(DATEDIF(TODAY(),A2,"MD")<15,DATEDIF(TODAY(),A 2)=0,"MD") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "jocker" wrote in message . nl... Using conditional formatting how would I highlight birthdays coming up within the next 14 days. I can't use < today()+14 since birth year is in the past. I don't mind using VBA if this helps |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight birthdays
Hi Arvi
Most unlike you. One or two typo's (must be the remainder of the Christmas "spirits" still in the system<vbg) Missing brackets after the Year(Today()) function =AND(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))= TODAY()) Also, Datedif error, should be =AND(DATEDIF(A2,TODAY(),"MD")<15,DATEDIF(A2,TODAY( ),"MD")=0) Datedif won't work in this scenario however, as the days part is giving the number of days over and above elapsed months for the period, not the days remaining. Best wishes for the New Year -- Regards Roger Govier Arvi Laanemets wrote: Hi With birth date in cell A2 =AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY() ,MONTH(A2),DAY(A2))=TODAY()) You also can try =AND(DATEDIF(TODAY(),A2,"MD")<15,DATEDIF(TODAY(),A 2)=0,"MD") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight birthdays
Another example
=AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))=TODAY( ),DATE(YEAR(TODAY()),MONTH (A1),DAY(A1))-TODAY()<=14) Roger, yours seems to highlight 1st and 2nd Feb. Haven't looked at why. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Roger Govier" wrote in message ... Hi There may be a simpler formula, but the following seems to work =AND(MONTH(A1)=MONTH(TODAY())+(DAY(A1)-DAY(TODAY())<0),DAY(A1)-DAY(TODAY())< =14) -- Regards Roger Govier jocker wrote: Using conditional formatting how would I highlight birthdays coming up within the next 14 days. I can't use < today()+14 since birth year is in the past. I don't mind using VBA if this helps |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight birthdays
0),DAY(A1)-DAY(TODAY())<
<0),DAY(A1)-DAY(TODAY())< wrote Hi There may be a simpler formula, but the following seems to work =AND(MONTH(A1)=MONTH(TODAY())+(DAY(A1)-DAY(TODAY())<0),DAY(A1)-DAY(TOD AY())< =14) -- Regards Roger Govier jocker wrote: Using conditional formatting how would I highlight birthdays coming up within the next 14 days. I can't use < today()+14 since birth year is in the past. I don't mind using VBA if this helps Hi Bob You're quite right. My formula is absolute rubbish, as it would also give incorrect results to any other days in the next month which are less than Today's DAY(). Yours is the far nicer (and correct) solution. Happy New Year to you. -- Regards Roger Govier Bob Phillips wrote: Another example =AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))=TODAY( ),DATE(YEAR(TODAY()) ,MONTH (A1),DAY(A1))-TODAY()<=14) Roger, yours seems to highlight 1st and 2nd Feb. Haven't looked at why. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Roger Govier" wrote in message |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight birthdays
Hi Bob
Apologies for that last response, I have made some changes to OE6 and it is putting some of my responses in weird positions relative to the original post It should have appeareed as You're quite right. My formula is absolute rubbish, as it would also give incorrect results to any other days in the next month which are less than Today's DAY(). Yours is the far nicer (and correct) solution. Happy New Year to you. -- Regards Roger Govier Roger Govier wrote: 0),DAY(A1)-DAY(TODAY())< <0),DAY(A1)-DAY(TODAY())< wrote Hi There may be a simpler formula, but the following seems to work =AND(MONTH(A1)=MONTH(TODAY())+(DAY(A1)-DAY(TODAY())<0),DAY(A1)-DAY(TOD AY())< =14) -- Regards Roger Govier jocker wrote: Using conditional formatting how would I highlight birthdays coming up within the next 14 days. I can't use < today()+14 since birth year is in the past. I don't mind using VBA if this helps Hi Bob You're quite right. My formula is absolute rubbish, as it would also give incorrect results to any other days in the next month which are less than Today's DAY(). Yours is the far nicer (and correct) solution. Happy New Year to you. -- Regards Roger Govier Bob Phillips wrote: Another example =AND(DATE(YEAR(TODAY()),MONTH(A1),DAY(A1))=TODAY( ),DATE(YEAR(TODAY()) ,MONTH (A1),DAY(A1))-TODAY()<=14) Roger, yours seems to highlight 1st and 2nd Feb. Haven't looked at why. -- HTH Bob Phillips (remove nothere from email address if mailing direct) "Roger Govier" wrote in message |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight birthdays
:-))
Thanks for correction! (I simply didn't have enough time to read what i did write - too much work) The formula with DATEDIF must be =IF(ISERROR(DATEDIF(TODAY(),A2,"MD")),FALSE,(DATED IF(TODAY(),A2,"MD")<15)) (the old one checked for past birthdays, not for coming ones, and missed error checking) -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) "Roger Govier" wrote in message ... Hi Arvi Most unlike you. One or two typo's (must be the remainder of the Christmas "spirits" still in the system<vbg) Missing brackets after the Year(Today()) function =AND(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))= TODAY()) Also, Datedif error, should be =AND(DATEDIF(A2,TODAY(),"MD")<15,DATEDIF(A2,TODAY( ),"MD")=0) Datedif won't work in this scenario however, as the days part is giving the number of days over and above elapsed months for the period, not the days remaining. Best wishes for the New Year -- Regards Roger Govier Arvi Laanemets wrote: Hi With birth date in cell A2 =AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY() ,MONTH(A2),DAY(A2))=TODAY()) You also can try =AND(DATEDIF(TODAY(),A2,"MD")<15,DATEDIF(TODAY(),A 2)=0,"MD") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight birthdays
Many thanks, Roger, it works a treat.
I'm new to VBA so thought I would try it. The code below however fails, can u tell me why ? ActiveCell.Formula = "=IF((C2)="""",""1"",IF(AND(A2)=""outdate"",(E2)< ""01/01/01"",(E2)<NOW()+14),""2"",IF(AND((a2)=""Birthday"" ,MONTH(e2)=MONTH(TODAY())+(DAY(e2)-DAY(TODAY())<0),DAY(e2)-DAY(TODAY())<=14),""YES"",""NO""))" Jeff +++++++++++++++++++ "Roger Govier" wrote in message ... Hi Arvi Most unlike you. One or two typo's (must be the remainder of the Christmas "spirits" still in the system<vbg) Missing brackets after the Year(Today()) function =AND(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))= TODAY()) Also, Datedif error, should be =AND(DATEDIF(A2,TODAY(),"MD")<15,DATEDIF(A2,TODAY( ),"MD")=0) Datedif won't work in this scenario however, as the days part is giving the number of days over and above elapsed months for the period, not the days remaining. Best wishes for the New Year -- Regards Roger Govier Arvi Laanemets wrote: Hi With birth date in cell A2 =AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY() ,MONTH(A2),DAY(A2))=TODAY()) You also can try =AND(DATEDIF(TODAY(),A2,"MD")<15,DATEDIF(TODAY(),A 2)=0,"MD") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Highlight birthdays
Maybe
Dim sFormula As String sFormula = "=IF(C2="""",""1"",IF(AND(A2=""outdate"",E2<" "01/01/01"",E2<NOW()+14)," & _ """2"",IF(AND(A2=""Birthday"",MONTH(E2)=MONTH(TODA Y())+(DAY(E2)-DAY(TODAY()) <0),DAY(e2)-DAY(TODAY())<=14)," & _ """YES"",""NO"")))" ActiveCell.Formula = sFormula -- HTH Bob Phillips (remove nothere from email address if mailing direct) "jocker" wrote in message . nl... Many thanks, Roger, it works a treat. I'm new to VBA so thought I would try it. The code below however fails, can u tell me why ? ActiveCell.Formula = "=IF((C2)="""",""1"",IF(AND(A2)=""outdate"",(E2)< ""01/01/01"",(E2)<NOW()+14 ),""2"",IF(AND((a2)=""Birthday"",MONTH(e2)=MONTH(T ODAY())+(DAY(e2)-DAY(TODAY ())<0),DAY(e2)-DAY(TODAY())<=14),""YES"",""NO""))" Jeff +++++++++++++++++++ "Roger Govier" wrote in message ... Hi Arvi Most unlike you. One or two typo's (must be the remainder of the Christmas "spirits" still in the system<vbg) Missing brackets after the Year(Today()) function =AND(DATE(YEAR(TODAY()),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY()),MON TH(A2),DAY(A2))=TODAY()) Also, Datedif error, should be =AND(DATEDIF(A2,TODAY(),"MD")<15,DATEDIF(A2,TODAY( ),"MD")=0) Datedif won't work in this scenario however, as the days part is giving the number of days over and above elapsed months for the period, not the days remaining. Best wishes for the New Year -- Regards Roger Govier Arvi Laanemets wrote: Hi With birth date in cell A2 =AND(DATE(YEAR(TODAY(),MONTH(A2),DAY(A2))-TODAY()<15,DATE(YEAR(TODAY() ,MONTH(A2),DAY(A2))=TODAY()) You also can try =AND(DATEDIF(TODAY(),A2,"MD")<15,DATEDIF(TODAY(),A 2)=0,"MD") -- Arvi Laanemets ( My real mail address: arvi.laanemets<attarkon.ee ) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Highlight all colums in Bar Graph | Charts and Charting in Excel | |||
Conditional Format to highlight entrire Row | Excel Worksheet Functions | |||
highlight cells equals sum, not count | Excel Discussion (Misc queries) | |||
How can I highlight every other group of data? | Excel Discussion (Misc queries) | |||
How do I highlight all lookup formulas in a worksheet in one shot. | Excel Discussion (Misc queries) |