PDA

View Full Version : Birthdays in Microsoft Works



01-09-1998, 11:11 AM
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.

Susan B
28-08-2002, 10:31 AM
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?

godfather
28-08-2002, 11:12 AM
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.

Susan B
28-08-2002, 01:24 PM
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!

Capt Jimbo
28-08-2002, 01:28 PM
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)

godfather
28-08-2002, 01:48 PM
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.

Capt Jimbo
28-08-2002, 02:34 PM
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? :-)

godfather
28-08-2002, 02:56 PM
Susan will need to test for a large number of birthdays though (mine, yours...)

Susan B
28-08-2002, 05:35 PM
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. :-)

Baldy
28-08-2002, 05:39 PM
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 :-)

godfather
28-08-2002, 05:48 PM
Susan, a "flag" is just a "1" or a "0" as an indicator for you to apply conditional formatting-or not.

i.e.if the "flag" cell is 1, you want to put conditional formatting on the cell, but if its 0, no formatting. Its not a special cell, its just a 1 or 0 as a factor to use in the next part.

Nobody has covered conditional formatting formula as we all thought you knew it (or looked it up in "help")

Format - conditional format

If Cell is -- Greater Than --- 0 select format button and choose format.

Now the cell you are conditionally formatting can be the "flag" cell, and if its not 0 it will be coloured as you have chosen.

If you want several dates, they should be off the screen, on a line of their own each, and you should sum them up to a total cell (say c24), and then your "flag" cell is just =c24. If you were testing for 5 birthdays and they all fell within the date window, the total would be 5 for that day, and the flag cell would read 5, which is also more that 0, so it still works.

Capt Jimbo
28-08-2002, 06:10 PM
What's your email Susan?
We can send your a spreadsheet each and you can pick whichever you like?
I'm still aiming to claim that chocolate fish :-)

If you don't want to publish your email here send it to my ICQ 9596596

Capt Jimbo

Susan B
28-08-2002, 06:23 PM
Was being a bit silly about the flags godfather, I did think they might be just sort of markers.

I can get the flags to change colour no problem but was trying to get the birthdates to change colour as well and I can't.

Am I right in surmising that your idea is to look at the flags and when they change colour (or if there is a 1 instead of a 0) then I know there's a birthday that week? Hmmm, it's not quite as sophisticated as I hoped but it will do the job.

But wait!! This is really weird but I've just tried changing "today's date" to various random samples and the flags now don't work! Gee this is complicated...

Capt'n my email is topazz10 at hotmail dot com if you'd like to have another go. Thanks!

godfather
28-08-2002, 06:32 PM
As the birthdates are already numbers, its a bit complicated to get them to change (possible but not nice), but you could have a flag cell next to them and have that change...

Wasn't sure what you wanted, but sounds like you have the basics mastered. Must say I have never used conditional formatting myself.

Hard enough managing a 54,000 row spreadsheet today, have to make a cup of coffee while it recalculates!

Susan B
28-08-2002, 10:24 PM
54,000 rows? Man, that would be a nightmare to manage. What if there was an error in one formula? :O

I can't imagine what you'd have in such a spreadsheet, but I do know I wouldn't like to be in charge of it...

Baldy
28-08-2002, 11:08 PM
> 54,000 rows? Man, that would be a nightmare to
> manage. What if there was an error in one formula?
> :O
>
> I can't imagine what you'd have in such a
> spreadsheet, but I do know I wouldn't like to be in
> charge of it...

LOL..... go on Susan, who are you kidding. All Women want to be in charge!

-=JM=-
28-08-2002, 11:23 PM
You just had to bump up the second thread on record here didn't you Susan.

Susan B
29-08-2002, 01:18 PM
> You just had to bump up the second thread on record
> here didn't you Susan.

:8} Where did that crystal ball come from JM? Come on, reveal your sources...

I got the surprise of my life when that post came up after fiddling - it was just what I have been wanting to know in the past couple of weeks.

Why reinvent the wheel when I already had one? :D

Baldy: women don't like to be in charge, they are in charge. ;-)

parry
29-08-2002, 09:29 PM
Hi Susan did you work this out? In case you didnt taking Godfathers formula (so all cf to him) do the following ...

Highlight the cells where you have the birthdays ( for example you can highlight all column a if this is where they are) then select conditional formatting. Choose the IsFormula option and enter the following formula...
=IF(ABS(A1-TODAY())<8,1,0)

Now select the font and colour red and press OK and your away. In this example I have put reference to cell a1 as the date even though I may have had dates in cell a5 as well. Because you selected all of column A before entering the formula in conditional formatting, Excel is correctly assuming you want the conditional formula based on reference a1, a2 , a3 etc.

I have changed Godfathers formula slightly to do away with the flags. Today() means return todays date.

Note that you will get #Name? errors or alike when dealing with dates unless you have correct regional settings - should be English(new Zealand)

cheers
Parry

Susan B
29-08-2002, 09:56 PM
Hi Parry, no, the virtual chocolate fish is still up for grabs as I didn't get it sorted.

Thanks for offering your version, I tried it and it works perfectly as long as the person was born this year. :D

If I put in any other year it stays the default. Obviously, like godfather says, I need to convert the year somehow but that's way over my head.

Thanks anyway. :-)

parry
29-08-2002, 10:04 PM
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
30-08-2002, 09:28 AM
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

Capt Jimbo
30-08-2002, 09:41 AM
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

Susan B
30-08-2002, 10:11 AM
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.

godfather
30-08-2002, 10:25 AM
burp.... thanks

})))><{

Susan B
30-08-2002, 10:32 AM
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!

parry
30-08-2002, 11:49 AM
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. :-)

Russell D
30-08-2002, 12:03 PM
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

Susan B
30-08-2002, 04:32 PM
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. :-)

parry
31-08-2002, 11:50 AM
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

antmannz
31-08-2002, 12:09 PM
Rather than work out years and months, why not just convert the date to a number?

I haven't used Excel for a while, but I know that each date is assigned a number starting with 1 Jan 1900 = 1. I think it even goes as far as hours and minutes, eg. midday 2 Jan 1900 = 1.5.

Therefore, it would innumerably easier to get the date serial and compare that to today's date. Actually, I think it's called DateSerial(Date).

So, the formula would be something like:
=IF(ABS(DATESERIAL(TODAY())-DATESERIAL(Susan's_date&month_only_birthday_cell))<8,true_statement,false_statement)

antmannz
31-08-2002, 12:19 PM
Ummm .... hang on, just realised that's all a load of garbage, it converts to numbers automatically & today() takes no account of years. Acckk :(

antmannz
31-08-2002, 12:37 PM
AH HA !!

Susan, the easiest formula is:
=DATEDIF(TODAY(),full_birthdate_including_year,"yd")
Change the colour of the cells based on whether the above formula produces a result of less than 7 or greater than 358 (to handle those with b/days between 24 Dec and 6 Jan).

See more here (http://www.cpearson.com/excel/datedif.htm).