Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dear all, I hope that you are there Steve.

    Attached please see a simple spreadsheet that allows me to remember people's birthdays.
    In the "F" column I have placed the year in which individuals were born.
    In cell "H1" I have placed the year 2010 - this gives me the age of the person in column "G"
    As I am getting older and not only forgetting birthdays, I also would like cell "H1" to change each year. When I use the =now() function in cell "H1", I obtain all sorts of numbers in column "G", numbers which do not reflect their ages.
    I have tried to customize cell "H1" by giving it the value of "yyyy", but that does not solve the problem either.
    I believe the formulae in column "G" to be valid ones.

    Would some kind soul modify this spreadsheet and explain what s/he has done to make it work.

    Regards Bruno Telingen
    Attached Files Attached Files

  2. #2
    5 Star Lounger
    Join Date
    Aug 2004
    Location
    Connecticut, USA
    Posts
    816
    Thanks
    0
    Thanked 0 Times in 0 Posts
    In H1 use the formula =Year(now()).

    Formatting the Now() to "yyyy" only changes what is displayed, not the underlying data.

    Dates, in Excel, are stored as numbers with the whole number portion is the number of days since 12/31/1899 while the decimal portion of the date is the part of the day since midnight.

    Today at 4:00pm or Feb 3, 2010 at 16:00 would be stored by Excel as 40212.66666. If you were to subtract the 2008 in F21 you end up with 38204 or 8/5/2004 or 2008 days ago.

  3. #3
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you very much, that is much better. I found it difficult to believe that some of my contemporaries were indeed 34000 years old - now I understand. I had not seen the formula written that way previously.

    Again, thank you.

    Bruno Terlingen

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    You can get by without H1 and calculate an age based on the current date with the formula in G2:
    =IF(F2,DATEDIF(DATE(F2,MONTH(A2),DAY(A2)),TODAY(), "y"),"")

    If desired you could also add conditional formatting to highlight the current day:
    Select A2:G368
    Format - conditional formatting
    Formula is:
    =TODAY()=DATE(YEAR(TODAY()),MONTH($A2),DAY($A2))
    [Format] - Patterns (tab)
    Select a color
    [ok][ok]

    Steve

  5. #5
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you Steve, I will certainly try these formula on another worksheet to increase my understanding. Then work out what I need for simplicity sake.

    Once again thank you, Bruno Terlingen

  6. #6
    Star Lounger
    Join Date
    Jan 2010
    Location
    Queensland Australia
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Dear Steve, I have used your "complicated" multiple nested IF statement with success. In one way the first option provided (in post 2) was "cleaner, but I tend to agree that the less cells with formalae the better the spreadsheet - so I have gone with your option.

    I love the conditional formatting. Yes I have used this facility before but certainly not with a "complicated" formula. At least I am clearly reminded what day/date it is - now I can grow old with confidence in that I will not forget people's birthdays and other important dates.

    Now all I ask you if you can set the spreadsheet up in such a manner so that it will remind me to send an email to the birthday boy/girl. Is there a sound which can be embedded in the conditional formatting that will sound if today a person has a birthday?

    Your'e champion, Bruno Terlingen

  7. #7
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,191
    Thanks
    48
    Thanked 986 Times in 916 Posts
    As an alternative to Excel I use Kalendar to store all my reminders etc, then it pops up a window X days/weeks before the event. I can set things to repeat, or as a one off, schedule things to run on particular days. All round great functionality for the price of a download.

    cheers, Paul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •