Now function in Excel

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote