PDA

View Full Version : Dates in Excel



Captn Andy
02-08-2003, 10:27 PM
I have a large collection of dates of birth that I want to enter into a spreadsheet and then deduct them from the present date to give current ages. Can anyone tell me if this can be done?

Also, is it possible to do the same thing in Access?

I am far from conversant with either application. That will teach me to promise to do something without having the faintest idea how I'm going to do it!

godfather
02-08-2003, 10:39 PM
Yes.
Excel will perform date arithmetic
Just subtract the birth date from today [=now()] will give today's date (leave out the square brackets)
Then format the cell the answer is in as a number (it will probably go to a date format by default which will be nonsense), which will be the number of days the person has been alive for.

Dividing that by 365.25 will give years in a simplified manner.

Captn Andy
02-08-2003, 10:57 PM
Thankyou Godfather. I had a suspicion it was possible. Does the date have to be formatted in any particular manner? (eg 28/6/45 or 28 June 1945)

parry
03-08-2003, 08:07 PM
Hi Captn Andy, dates can be formatted as 3/8/03 for ease, but if you look under format-cells and the category of date you will see there are many variations you can use.

Excel uses serial numbers to identify dates, with each day being a whole number with 1/1/1900 being 1 and every day after that another number.

If you want to calculate the age of someone use the DATEDIF function as per this Link (http://www.cpearson.com/excel/datedif.htm#Age).

Access can do this too but Excel is more suited to calculations that Access and Access alpse takes more effort to set up. For something simple like this just use Excel.

hth

Captn Andy
03-08-2003, 10:15 PM
My thanks, Parry. The info in the link proved most helpful and I must admit that I have learned more about Excel in one day than I learned about my wife in 25 years!