Help with XDATE function add-in

[Mary]

I'm working with a spreadsheet that has a column for date of birth and of
course bumped up against the pre-1900 date issue. Some of the dates are
pre-1900 and some are post-1900. In another group of columns we want to
calculate ages of individuals over a number of years. Looking online, I
found John Walkenbach's XDate add-in at
http://j-walk.com/ss/excel/files/xdate.htm. I've downloaded it and installed
is as instructed. However, I can't figure out how to use it.

I'm using Excel 2003 on Windows XP.

Instructions say "You can type the functions manually, or use Excel's Paste
Function dialog box. To access the Paste Function dialog, click the Paste
Function button..." Is that the Insert Function button?

"or select Insert - Function. The XDate Functions are listed in the 'Date &
Time' Category. When a function is selected in the Paste Function dialog,
press F1 to read the online help." I've tone to Insert-->Function. The
Insert Function window opens (not the Paste Function shown in his
screengrab). I choose the category Date&Time and select the function XDATE.
When I hit OK, I'm not sure how to use the Function Arguments window. For
the date 11/19/1893, opposite Y, M and D, I filled in 1893, 11, 19 and I
left the Fmt field blank. The function is shown as =XDATE(1893, 22, 19).
Although the cell contents display 11/19/1893, Excel is not seeing it as a
date, it seems to be still treated as text. Calculations based on the date
don't work and changing the cell to a different date format has no effect.

Can someone explain to me how to use the function properly in simple terms
as I'm only a basic user of Excel. Thank you.

 

[Bob Phillips]

Excel will not see it as a date, they only recognise dates after 31st Dec
1899. You have to use other of John's functions such as XDateAdd, and you
have to use the functions formatting parameters to get a different format.

What calculations are you trying?

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Mary]

The data is a membership list for aan organizaion that was founded in the
early 1900s and continues through today. The date of birth of members spans
about 140 years, so the XDATE will have to be used in that column, at least
for the pre-1900 dates. There are also entries for Date Admitted to Org and
Date of Death.

One calculations they need is a column to show Age at Admission. I find that
the XDATEYEARIF function works for me there, in a test the entry "
=XDATEYEARDIF(G318,M318)" returned the correct age where G318 represented
date of birth and M318 represented date of admission. Would you suggest
using the XDATEYEARIF function only on the pre-1900 dates or on all dates. I
know it will be tedious making all the entries manually, but at least if
they all use the same function, the same formulae can be used for all the
calculations.

They also want to calculate the Total Number of Active Members Each Year and
the Average Age of Active Members Each Year. Their intention is to use that
data in the form of graphs(s) to show how the membership had changed over
time.

For individual members ages in particular years, the XDATEYEARIF function
works again.when I subtract year of birth from the given year. However, I
need to figure out how to return no value if either (1) the person in
question has not yet joined, or (2) the person has died.

 

[Bob Phillips]

Mary,

I think it makes sense for you to always use XDATE functions for this list,
it will be less confusing (maybe a small textbox note off to the side for
anyone else). John's functions work just as well with real dates as the text
pre-1900 dates, so you should be okay.

You can test for those criteria like so,

=IF(OR(date_of_admission="",date_of_death<>""),"",XDATEYEARDIF(date_of_birth,date_of_admission))


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)