Forum Home
Press F1
Thread ID: 2	1998-09-01 00:11:00	Birthdays in Microsoft Works	Guest (0)	Press F1

Post ID	Timestamp	Content	User
74	2002-08-29 11:04:00	Duh! I see what your after ... I was just using literal dates which would be fine if you wanted to know birthdays for newborns LOL. Should be easy enough, I can think of a couple of ways but would like to test them out beforehand. Feeling a bit tired so maybe tomorrow. Parry	parry (27)
75	2002-08-29 22:28:00	Hi all, Im a bit less tired now so have had a think about this problem. As I see it (Capt Jimbo / Godfather can correct me) you first need to do conversions before doing the date comparisons. So the birthday year needs to be converted to the current year except in the following circumstances :- *Where birthday month = 12 & current month = 1 then birthday year = current year - 1 *Where birthday month = 1 & current month = 12 then birthday year = current year + 1 The following needs to happen IMHO 1) Turn the birthday and current date into text strings then extract the months to do a compare to determine what the year figure is going to be. 2) Next the current year will need to be converted to a number so addition/subtraction can be achieved. 3) Amend birthday to existing dd/mm with new yyyy 4) Turn birthday and current date back into dates (or a date serial) to perform the comparison If you were to do this via formulas then you would need to use several cells as each cell may only have one format. I think the easiest way to achieve this is via code. Susan, I am only a learner at VBA but I can have a crack at it for you if you let me know the range where you have the dates. This is perhaps a good question for http://www.mrexcel.com Cheers Parry	parry (27)
76	2002-08-29 22:41:00	I must also confess that it isn't as simple as I had hoped. My love of chocolate fish is spurring me on but I've had to make sure I'm not distracting myself from the duties I'm employed to do :-) I'm fairly certain it can be done with a formula in conditional formatting but as you say Parry there are the difference in years to account for. If I get the answer I'll let everyone know. Capt Jimbo	Capt Jimbo (17)
77	2002-08-29 23:11:00	If I had known it was going to be this hairy I wouldn't have asked in the first place. Sorry guys! I thought there would be just a couple of quick formulas that I wasn't familiar with but obviously that's not the case. It's not important so don't waste any more of your time on it. What I can do is put in another column and enter just the day and month of people's birthdates and try using that with the conditional formatting that you've given me so far. That will be sufficient. In any case, you all deserve a virtual chocolate fish for trying so hard, so here you go, one each: <*)))))><{ <*)))))><{ <*)))))><{ Don't squabble over them, they are all the same size.:D If any of you want a real chocolate fish then email your address to me and I'll send you one. Thanks a lot guys, I appreciate your efforts.	Susan B (19)
78	2002-08-29 23:25:00	burp.... thanks })))><{	godfather (25)
79	2002-08-29 23:32:00	LOL :D Just letting you all know what I've done: 1. Created another column, entered day and month of birthdates and hid this column. 2. Conditionally formatted the person's name with Parry's formula to change to red text when the person's birthday falls within a week either side of today's date. Works great! You've earnt your chocolate fish guys!	Susan B (19)
80	2002-08-30 00:49:00	Cool glad you got it sussed. As a matter of interest whats the formula in the hidden clumn and how have you got the conditional format formula referencing this? Could you post the formulas please. When I have some free time I might work out how to do this with code anyway as it will be a good learning exercise. Some rainy day thing. :-)	parry (27)
81	2002-08-30 01:03:00	This works - If as per the original post and replies - =Today() in A1 and birthdays L6:L28, If you enter the following formula in A6 and copy down to A28 =IF(ABS($A$1-DATE(YEAR(TODAY()),MONTH(L6),DAY(L6)))<=7,1,0) The Conditional Format Formula for A1 is =SUM(A6:A28)>0	Russell D (18)
82	2002-08-30 05:32:00	Thanks for that Russell . Your method would save me having to have two columns of birthdates (one with the year and one without) but I find it a little more fiddly to set up with the spreadsheet that I've got compared to Parry's formula . Having seen your formula though, I'm going to try something with it so your time wasn't wasted . :-) Parry, what I've done is . . . hmm, let me think what I did - I can't remember now! Oh yes, the birthdates in 23/08/64 format are in L6:L28 and in 23/08 format in M6:M28 . The conditional format formula of =IF(ABS(M1-TODAY())<8,1,0) is in the first name column, C, and also the birthdates column, L . Column M is hidden . Both the person's first name and their birthdate (in column L) of 23/08/64 is highlighted in red and that is just what I want it to do . :-)	Susan B (19)
83	2002-08-31 00:50:00	Very clever Russell! I was wondering how the Year(today()) piece was working as per my post if you have dates in Dec & Jan. The formula works if the current month is Jan and birthday is in Dec, but not if current month is Dec and birthday in January. I think this calls for nested if statements in the formula to check for this <shudder>. I must admit I get confused when doing nested if statements as my brackets are all over the place. :-) Do you know how to amend the formula to cover the Dec/Jan months? cheers Parry	parry (27)
1 2 3 4