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
54	1998-09-01 00:11:00	I am using Windows 3.11. I have a Microsoft Works database which is a list of people and information about them. One of the fields in the database is BIRTHDAYS, which contains dates in dd//mm/yy format. I wish to have a query which gives me a list of all the people who have a birthday in a particular month I have tried to use the function MONTH in the following syntax in the 'value is equal to' area of the query form: =MONTH()=8 (assuming August is the month requested). This gives me an error. I have tried many variations without success.	Guest (0)
55	2002-08-27 23:31:00	I've got a similar query, except that I'm using Win98 and Excel 97. I want to have a formula in which the birthday is displayed in red whenever it falls during the 7 days prior and after the actual date. For example, if someone's birthday is today, I want the (today's) date highlighted in red text from last Wednesday to next Wednesday to draw my attention to the fact that someone has/had a birthday today. Is this possible in Excel?	Susan B (19)
56	2002-08-28 00:12:00	Yes Use conditional formatting on the date cell, and use normal aritmetic comparison on the date. =if(abs(todaysdate-birthdate)<8 then...)put conditional formatting here.. Probably easier to set a cell flag (1 or 0) out of sight for the trigger to format the cell.	godfather (25)
57	2002-08-28 02:24:00	Thanks godfather, but I'm obviously too thick to get it right. Tried all things I can think of but Excel doesn't like my formula with <8 in it. With quotes, without quotes, or any other flavours I try. If I have a date in cell A1, eg 26/08/72 I want the text colour to be red if I open the workbook today or within 7 days either side of 26/08/02. Next Thursday when I open the workbook (or if I change the date to something other than within 7 days either side of 26/08/02) I want the text colour to be the default black text. If anyone can brew up a formula for this in either cell A1 or cell A2 and can send me the Excel spreadsheet in an Excel 97 format I'll give them a virtual chocolate fish!	Susan B (19)
58	2002-08-28 02:28:00	You're going to have to make an allowance for the difference in years. The formula with <8 is only looking at 8 days difference. You're expecting it to look at 30 years. I'm a little tight for time right now but if I get the chance I'll have a go at claiming that chocolate fish (mmmmmm....... chocolate)	Capt Jimbo (17)
59	2002-08-28 02:48:00	OK Assume todays date is A1 Your reference birthday date is in A22 You want to set a flag (1 or 0) in A24 The formula for A24 is: =IF(ABS(A1-A22)<7,1,0) So A24 (or wherever you want the flag) is 1 for 7 days either side of A1 (OK you cant go less as its column A, but as you copy it across...) Make the "7" any number as thats the days + or - the flag will be set. Then use the 1 or 0 to set the conditional format. This will require a line (A22) off the screen for each birthday, but you could summate the flags and use that as a flag for triggering the conditional format Tried it, it works.	godfather (25)
60	2002-08-28 03:34:00	Cell A1 is: =TODAY() Cell 2 is conditionally formatted with: =AND(MONTH($A$1)=MONTH(A2),DAY($A$1)<7>DAY(A2)) (And the format you want. ie Red font) You should then be able to copy the Condition Formatting using paste special. Have I earnt a Choccy Fish yet? :-)	Capt Jimbo (17)
61	2002-08-28 03:56:00	Susan will need to test for a large number of birthdays though (mine, yours...)	godfather (25)
62	2002-08-28 06:35:00	I've still got no red text!! :_| :_| :_| You're dealing with a basic skills Excel user here... Sorry Capt'n but your formula does zilch in my spreadsheet. I know I've got it wrong somehow so it won't be your fault. godfather, your method works fine... until I get to the flags. I can't see any flags, are they supposed to be blue, red and white? They must be white as they are very well hidden. I've got the birthdates in cells L6 to L28 and I put =TODAY() in L4. Your formula =IF(ABS(A1-A22)<7,1,0) goes in M6 to M28 and changes to 0 or 1 depending on the birthdate. So far, so good. Now I get stuck applying the conditional formatting. I can make the text change to red for the M6 to M28 cells when the number is 1 but how do I tie it to the birthdates in L6 to L28 to make them red as well? I don't understand flags very well. If you might send me your spreadsheet godfather, I will understand a lot better with something to look at... :-) My shoe size is 9½ remember? Both of your efforts to help are much appreciated. :-)	Susan B (19)
63	2002-08-28 06:39:00	Ummm computers are great for "most" things, but in the old days they used to write down the birthday lists in a diary or notebook BALDY :-)	Baldy (26)
1 2 3 4