Highlighting upcoming birthdays

[doherty22]

Hello,

I have a spreadsheet with one column with DOBs and the next column uses this this work out their age.

Is there way I can use conditional formatting to highlight the 'age' cell in red when a big birthday (say, 50, 60, 75) is within 3 months?

Thanks

 

[etaf]

a few ways to do that
how are you working age out

I'm using
=AND(OR(INT(B2)=50,INT(B2)=65,INT(B2)=70),(B2-INT(B2))<0.25)

so the OR ( INT(B2)=50,INT(B2)=65,INT(B2)=70) is testing if a significant birthday - what is the full list - maybe a table of significant ages would work better

OR
=DATE(YEAR(TODAY()),MONTH(A2)-3,DAY(A2))
with the test for a particular age

and use as a conditional formatting rule

BUT I would like to see you examples and expected results

Book2
	A	B	C	D	E	F	G	H
1	DOB	AGE	TEST				3month	test
2	1/1/56	68.3456731	FALSE				10/1/23	TRUE
3	2/1/57	67.2607803	FALSE				11/1/23	TRUE
4	3/1/58	66.1841288	FALSE				12/1/23	TRUE
5	4/1/59	65.0992658	TRUE				1/1/24	TRUE
6	5/1/60	64.0144049	FALSE				2/1/24	TRUE
7	6/1/61	62.9322382	FALSE				3/1/24	TRUE
8	7/1/62	61.8501152	FALSE				4/1/24	TRUE
9	8/1/63	60.7652566	FALSE				5/1/24	TRUE
10	9/1/64	59.6776626	FALSE				6/1/24	FALSE
11	10/1/65	58.5982204	FALSE				7/1/24	FALSE
12	11/1/66	57.5133643	FALSE				8/1/24	FALSE
13	12/1/67	56.4312485	FALSE				9/1/24	FALSE
14	1/1/69	55.3456537	FALSE				10/1/23	TRUE
15	2/1/70	54.2607895	FALSE				11/1/23	TRUE
16	3/1/71	53.1841411	FALSE				12/1/23	TRUE
17	4/1/72	52.0965442	FALSE				1/1/24	TRUE
18	5/1/73	51.0171116	FALSE				2/1/24	TRUE
19	6/1/74	49.9322525	FALSE				3/1/24	TRUE
20	7/1/75	48.8501342	FALSE				4/1/24	TRUE
21	8/1/76	47.7625433	FALSE				5/1/24	TRUE
22	9/1/77	46.6803559	FALSE				6/1/24	FALSE
23	10/1/78	45.5982408	FALSE				7/1/24	FALSE
24	11/1/79	44.5133913	FALSE				8/1/24	FALSE
25	12/1/80	43.4285454	FALSE				9/1/24	FALSE
26	1/1/82	42.3456641	FALSE				10/1/23	TRUE
27	2/1/83	41.2608044	FALSE				11/1/23	TRUE
28	3/1/84	40.1814236	FALSE				12/1/23	TRUE
29	4/1/85	39.0992471	FALSE				1/1/24	TRUE
30	5/1/86	38.0171288	FALSE				2/1/24	TRUE
31	6/1/87	36.9322767	FALSE				3/1/24	TRUE
32	7/1/88	35.8474288	FALSE				4/1/24	TRUE
33	8/1/89	34.7652293	FALSE				5/1/24	TRUE
34	9/1/90	33.6803817	FALSE				6/1/24	FALSE
35	10/1/91	32.5982768	FALSE				7/1/24	FALSE
36	11/1/92	31.5107018	FALSE				8/1/24	FALSE
37	12/1/93	30.4312115	FALSE				9/1/24	FALSE
38	1/1/95	29.3456835	FALSE				10/1/23	TRUE
39	2/1/96	28.2608326	FALSE				11/1/23	TRUE
40	3/1/97	27.1841205	FALSE				12/1/23	TRUE
41	4/1/98	26.0992699	FALSE				1/1/24	TRUE
42	5/1/99	25.0171633	FALSE				2/1/24	TRUE
43	6/1/00	23.9295883	FALSE				3/1/24	TRUE
44	7/1/01	22.8501027	FALSE				4/1/24	TRUE
45	8/1/02	21.765266	FALSE				5/1/24	TRUE
46	9/1/03	20.680438	FALSE				6/1/24	FALSE
47	10/1/04	19.5956199	FALSE				7/1/24	FALSE
48	11/1/05	18.513347	FALSE				8/1/24	FALSE
49	12/1/06	17.431268	FALSE				9/1/24	FALSE
50	1/1/08	16.3457327	FALSE				10/1/23	TRUE
51	2/1/09	15.2607803	FALSE				11/1/23	TRUE
52	3/1/10	14.1841577	FALSE				12/1/23	TRUE
53	4/1/11	13.0993352	FALSE				1/1/24	TRUE
54	5/1/12	12.0145294	FALSE				2/1/24	TRUE
55	6/1/13	10.9322382	FALSE				3/1/24	TRUE
56	7/1/14	9.85017422	FALSE				4/1/24	TRUE
57	8/1/15	8.7653983	FALSE				5/1/24	TRUE
58	9/1/16	7.67791971	FALSE				6/1/24	FALSE
59	10/1/17	6.5982204	FALSE				7/1/24	FALSE
60	11/1/18	5.51349237	FALSE				8/1/24	FALSE
61	12/1/19	4.43156934	FALSE				9/1/24	FALSE
62	1/1/21	3.34565366	FALSE				10/1/23	TRUE
63	2/1/22	2.26094891	FALSE				11/1/23	TRUE
64	3/1/23	1.18467852	FALSE				12/1/23	TRUE
65	4/1/24	0.09836066	FALSE				1/1/24	TRUE
Sheet1

Cell Formulas
Range	Formula
B2:B65	B2	=YEARFRAC(A2, TODAY(), 1)
C2:C65	C2	=AND(OR(INT(B2)=50,INT(B2)=65,INT(B2)=70),(B2-INT(B2))<0.25)
G2:G65	G2	=DATE(YEAR(TODAY()),MONTH(A2)-3,DAY(A2))
H2:H65	H2	=TODAY()>DATE(YEAR(TODAY()),MONTH(A2)-3,DAY(A2))

A good sample workbook has just 10-20 rows of representative data that has been desensitised. The sample layout accurately matches that of your real data. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

This will possibly enable a quicker and more accurate solution for you.

MrExcel has a tool called “XL2BB” that lets you post samples of your data and will allow us to copy/paste your sample data into our Excel spreadsheets, saving a lot of time.

XL2BB - Excel Range to BBCode

Excel 'mini-sheet' in messages - XL2BB Although experts prefer to read your description and question instead of working in your actual file to solve your problem, there are times that it is difficult to explain an issue without providing actual...

www.mrexcel.com

You can also test to see if it works ok, in the "Test Here" forum.

Test Here

Use this forum to test your signature, learn bbcode, smilies, XL2BB, etc. Threads in this forum are automatically deleted after no replies for seven (7) days

www.mrexcel.com

OR if you cannot get XL2BB to work, or have restrictions on your PC

then put the sample spreadsheet onto a share

I only tend to goto OneDrive, Dropbox or google docs , as I'm never certain of other random share sites and possible virus.
Please make sure you have a representative data sample and also that the data has been desensitised, remember this site is open to anyone with internet access to see - so any sensitive / personal data should be removed

Make sure you set any share or google to share to everyone

if you have any issues with XL2BB, then the "About this Board" is the forum to ask questions about XL2BB

About This Board

Discussion of policies, bug reports, how to use it, XL2BB questions, etc.

www.mrexcel.com

 

[doherty22]

I am using =DATEDIF(B2,TODAY(),"y") to work out their age

Name	DOB	Age
	27/08/1954	69
	30/05/1958	65
	12/06/1962	61
Mr X	01/06/1974	49
	04/04/1963	61
	13/11/1951	72
	21/10/1953	70
Ms Y	30/05/2003	20
	23/07/1954	69
	03/01/1953	71
	24/01/1966	58
	09/09/1983	40
Ms Z	13/05/1944	79
Mr A	17/05/1994	29
	17/04/1966	58
	24/12/1966	57
	26/10/1970	53
	04/12/1985	38
	15/09/1955	68
	13/09/1990	33
	12/03/1984	40
	14/09/1948	75
	16/12/1978	45
	18/01/1974	50
	24/02/1963	61
	13/07/1981	42
	11/02/2001	23
	03/03/1959	65
	11/07/1971	52

For an example what I would like is that the ages for Mr X, Ms Y, Ms Z, and Mr A flag up in red as they are going to turn 50,21 80, and 30 respectively within 2 months.


Thank you

 

[etaf]

this is really messy formula - and other members may see a better way

=IF(B2="","",AND(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),OR(DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=21,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=30,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=50,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=80)))
and added the formula into a helper column in example column F as the main formula will not work in conditional formatting formula

then i have used in conditional formatting
=$F2

as i say , i'm sure a simplier way - perhaps using a lookup table or countif to check for the age 21,30,50 &80

i have to go out , and so will look into that as a better solution

perhaps meanwhile you could test with other examples just to make sure it is working

Book4
	A	B	C	D	E	F
1	Name	DOB	Age			HELPER
2		8/27/54	69			FALSE
3		5/30/58	65			FALSE
4		6/12/62	61			FALSE
5	Mr X	6/1/74	49			TRUE
6		4/4/63	61			FALSE
7		11/13/51	72			FALSE
8		10/21/53	70			FALSE
9	Ms Y	5/30/03	20			TRUE
10		7/23/54	69			FALSE
11		1/3/53	71			FALSE
12		1/24/66	58			FALSE
13		9/9/83	40			FALSE
14	Ms Z	5/13/44	79			TRUE
15	Mr A	5/17/94	29			TRUE
16		4/17/66	58			FALSE
17		12/24/66	57			FALSE
18		10/26/70	53			FALSE
19		12/4/85	38			FALSE
20		9/15/55	68			FALSE
21		9/13/90	33			FALSE
22		3/12/84	40			FALSE
23		9/14/48	75			FALSE
24		12/16/78	45			FALSE
25		1/18/74	50			FALSE
26		2/24/63	61			FALSE
27		7/13/81	42			FALSE
28		2/11/01	23			FALSE
29		3/3/59	65			FALSE
30		7/11/71	52			FALSE
Sheet1

Cell Formulas
Range	Formula
C2:C30	C2	=DATEDIF((DATE(YEAR(B2),MONTH(B2),DAY(B2)) ),TODAY(),"y")
F2:F30	F2	=IF(B2="","",AND(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),OR(DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=21,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=30,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=50,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=80)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A30	Expression	=$F2	text	YES

i have added the spreadsheet onto a dropbox share - will only be available for a few days

age-confrmt-ETAF.xlsx

Shared with Dropbox

www.dropbox.com

 

[etaf]

you could use a LET as you have 365 version
=LET( DDF, DATEDIF(DATE(YEAR(B2),MONTH(B2)-2,DAY(B2)),TODAY(),"y"), AND(DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>=DDF,OR(DDF=21,DDF=30,DDF=50,DDF=80)))
and that will work in a conditional formatting formula

 

[doherty22]

Thank you, the only issue I'm having is that it is still flagging as red once the birthday has passed. Do you know if there is a fix for that?

Thanks again

 

[etaf]

yep, still very messey
=IF(B2="","",AND(TODAY()< DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)), DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),OR(DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=21,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=30,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=50,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=80)))
seems to work - but not exhaustively tested - just on Mrs Z - change date to 1/5/44

age-confrmt-ETAF2.xlsx

Shared with Dropbox

www.dropbox.com

age-confrmt-ETAF2.xlsx
	A	B	C	D	E	F
1	Name	DOB	Age			HELPER
2		8/27/54	69.00			FALSE
3		5/30/58	65.00			FALSE
4		6/12/62	61.00			FALSE
5	Mr X	6/1/74	49.00			TRUE
6		4/4/63	61.00			FALSE
7		11/13/51	72.00			FALSE
8		10/21/53	70.00			FALSE
9	Ms Y	5/30/03	20.00			TRUE
10		7/23/54	69.00			FALSE
11		1/3/53	71.00			FALSE
12		1/24/66	58.00			FALSE
13		9/9/83	40.00			FALSE
14	Ms Z	5/1/44	80.00			FALSE
15	Mr A	5/17/94	29.00			TRUE
16		4/17/66	58.00			FALSE
17		12/24/66	57.00			FALSE
18		10/26/70	53.00			FALSE
19		12/4/85	38.00			FALSE
20		9/15/55	68.00			FALSE
21		9/13/90	33.00			FALSE
22		3/12/84	40.00			FALSE
23		9/14/48	75.00			FALSE
24		12/16/78	45.00			FALSE
25		1/18/74	50.00			FALSE
26		2/24/63	61.00			FALSE
27		7/13/81	42.00			FALSE
28		2/11/01	23.00			FALSE
29		3/3/59	65.00			FALSE
30		7/11/71	52.00			FALSE
Sheet1

Cell Formulas
Range	Formula
C2:C30	C2	=DATEDIF((DATE(YEAR(B2),MONTH(B2),DAY(B2)) ),TODAY(),"y")
F2:F30	F2	=IF(B2="","",AND(TODAY()< DATE(YEAR(TODAY()),MONTH(B2),DAY(B2)), DATE(YEAR(TODAY()),MONTH(B2),DAY(B2))>=DATE(YEAR(TODAY()),MONTH(TODAY())-2,DAY(TODAY())),OR(DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=21,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=30,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=50,DATEDIF((DATE(YEAR(B2),MONTH(B2)-2,DAY(B2))),TODAY(),"y")=80)))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
A2:A30	Expression	=$F2	text	NO