Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with XDATE function add-in
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with XDATE function add-in
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" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with XDATE function add-in
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" wrote in message ... 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" wrote in message ... 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Help with XDATE function add-in
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) "Mary" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy of excel file not showing formulal/function in the function b | Excel Discussion (Misc queries) | |||
LINKEDRANGE function - a complement to the PULL function (for getting values from a closed workbook) | Excel Worksheet Functions | |||
Offset function with nested match function not finding host ss. | Excel Worksheet Functions | |||
Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Nested IF Function, Date Comparing, and NetworkDays Function | Excel Worksheet Functions |