A couple of observations. I believe the calculations would depend on how you define a month (calendar or average elapsed days) and how you assign the month value. Do you assign February and May the same value even when one is 28 days long and the other 31?...there is also leap years…
For example: DOB = 01/01/2022 DOT = 03/01/2023 Your formula will give you a value of 1 year and 1 month based on 424 elapsed days, even when the calendar shows 2 months, i.e. January-March. The value would not change until you hit the 4th day of March (437 elapsed days) when it would show 2 months. Taking it to an extreme, what do you assign a when the DOB and DOD are in the same year and same month? Do you assign 0 months or 1 month? Obviously going to a year-month-day would be more accurate but might be an overkill for your needs. If all you need is a good approximation, your approach is plenty good, otherwise, further calculation will be needed. Some function that you might want to look at: IMON(.vDate) gives you the month for the variable .vDate IYR(.Date) gives you the year for the variable .vDate JDATE(.vDate) gives you the Julian date with the format YYYYnnn where “YYYY” is the year for variable .vDate and “nnn” is the day number for that year and you can easily extract the values for calculations. Unfortunately, we don’t have (yet) a function comparable to Excel DATEDIF. We engineers tend to look not only at the big picture but also the small details, and often it can be seen as making it more complicated that need be; however, the little details that might never happen are the ones can bite you in the rear end when they do. Hopefully, the information is of use to you. Javier, Javier Valencia, PE 14315 S Twilight Ln Olathe, KS 66062 913-915-3137 From: [email protected] <[email protected]> On Behalf Of Charles Stevens Sent: Sunday, November 12, 2023 2:38 PM To: [email protected] Subject: Re: Re[2]: [RBASE-L] - Calculating Age in Years, Months | PRIVATE Thanks, Randy - I was interested in the structure of the formula and wanted to see if anyone had a simpler approach to achieve the results. I have apx 152 of 6,300 rows containing fields with data in the database. The report gives fairly accurate results using my clumsy formula on which I spend an inordinate amount of time and effort. I have concluded, based somewhat on Bruce's response, to stick with what I have and that there is no simpler formula, such as the DATEDIF found in Excel. Thanks again for your interest and input. Cheers, Chuck On Sat, Nov 11, 2023 at 11:46 PM randyp ctags.com <http://ctags.com> <[email protected] <mailto:[email protected]> > wrote: If you are doing this for every row in your database, you might create a view rather than calculate row by row. You could add the Y & M as part of your report fields. You could limit your view to "WHERE DeathDateTime IS NOT NULL" , or "WHERE DeathDateTime EXISTS." Might something like this work? CREATE VIEW LifeYrMo (YrsLife, MosLife) AS SELECT ( (DeathDateTime-BirthDateTime)/365.25), ( ( (MOD(YrsLife))*365.25)/30.44) FROM ... Randy _____ From: [email protected] <mailto:[email protected]> <[email protected] <mailto:[email protected]> > on behalf of Bruce Chitiea <[email protected] <mailto:[email protected]> > Sent: Saturday, November 11, 2023 9:43 AM To: [email protected] <mailto:[email protected]> <[email protected] <mailto:[email protected]> > Subject: Re[2]: [RBASE-L] - Calculating Age in Years, Months | PRIVATE I apologize for using the word "ugly" <insert excuse here>. Rather, your code is elegant in that it really could not be reduced further without one helluva lot of work, as the 2016 approach suggests. I should have remembered my 9th grade geometry final, where, confronted with a jungular tangle of lines, I brute-forced my way to QED through thisHere angle equals thatThere angle in 23 steps. Thought I was all that. The girl next to me reached QED in three. As to being more versed ... doubtful. Best, Bruce ------ Original Message ------ >From "Charles Stevens" <[email protected] <mailto:[email protected]> > To [email protected] <mailto:[email protected]> Date 11/11/2023 7:29:29 AM Subject Re: [RBASE-L] - Calculating Age in Years, Months Hi, Bruce - Thanks for your time and response. I spent an inordinate amount of time and effort to create a working formula, and thought there must be an easier way. I had in mind a function similar to DATEDIF in Excel. Glad to have the opinion of someone more versed in R:Base than I am. I will use it - as is - in all its ugliness, since it works. Cheers, <http://img194.imagevenue.com/loc254/th_139623767_chuckstevens_122_254lo.jpg> On Fri, Nov 10, 2023 at 10:24 PM Bruce Chitiea <[email protected] <mailto:[email protected]> > wrote: Charles: Played with it a bit, searching for elegance. The only fully duplicated segment 'DOD - DOB' is wrapped on all sides by unique function strings, so atomization likely creates more work than it's worth. So, ugly it may be; but if it works ... Alternately, from the list in 2016: CLEAR VAR v% CLS SET VAR vBegDATE DATE = NULL SET VAR vEndDATE DATE = NULL SET VAR vBegTIME TIME = NULL SET VAR vEndTIME TIME = NULL SET VAR vBegDATE = (RDATE(4,21,2016)) SET VAR vEndDATE = (RDATE(4,27,2016)) SET VAR vBegTIME = (RTIME(10,20,0)) SET VAR vEndTIME = (RTIME(14,15,0)) SET VAR vBegDATETIME = (DATETIME(.vBegDATE, .vBegTIME )) SET VAR vEndDATETIME = (DATETIME(.vEndDATE, .vEndTIME )) SET VAR vElapsed_MIN = ((.vEndDATETIME - .vBegDATETIME)/60) SET VAR vElapsed_HR = (ROUND((.vElapsed_MIN/60),3)) SET VAR vElapsed_DAY = (ROUND((.vElapsed_HR/24),3)) SHOW VARIABLES V% RETURN Maybe there's some gold there. Best, Bruce Bruce A. Chitiea SafeSectors, Inc. 1142 S Diamond Bar Blvd # 442 Diamond Bar CA 91765-2203 [email protected] <mailto:[email protected]> (909) 238-9012 m ------ Original Message ------ >From "Charles Stevens" <[email protected] <mailto:[email protected]> > To [email protected] <mailto:[email protected]> Date 11/10/2023 1:36:33 PM Subject [RBASE-L] - Calculating Age in Years, Months Hello All - I am wondering if there is a more elegant way to find the difference between two days expressed as Years, Months, than what I have been using, below, which produces the results nny nnM, but seems clumsy: (CTXT(INT((DOD - DOB)/365.25))+'Y')&(CTXT(INT(MOD(DOD - DOB,365.25)/30.44))+'M') Where 'DOD'=Date of Death, 'DOB'=Date of Birth, 365.25=number of days in a year, and 30.44=number of days in a month. Seems to me this should be a commonly needed calculation. <http://img194.imagevenue.com/loc254/th_139623767_chuckstevens_122_254lo.jpg> -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/CADAyCN_2mA%3DZmh3jgYnEaRDZ3JWgudXfBNTdP_Km704YLuKGEA%40mail.gmail.com <https://groups.google.com/d/msgid/rbase-l/CADAyCN_2mA%3DZmh3jgYnEaRDZ3JWgudXfBNTdP_Km704YLuKGEA%40mail.gmail.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/emb1486432-408d-4603-94dc-fdd6aa373662%40ffc94789.com <https://groups.google.com/d/msgid/rbase-l/emb1486432-408d-4603-94dc-fdd6aa373662%40ffc94789.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/CADAyCN-eDQc%2B20aCaacxJth%3D3B-7aajOAO4kt9qy-3MfTqO9tA%40mail.gmail.com <https://groups.google.com/d/msgid/rbase-l/CADAyCN-eDQc%2B20aCaacxJth%3D3B-7aajOAO4kt9qy-3MfTqO9tA%40mail.gmail.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/em5c0c0571-c57b-4b66-b148-a7199a992f97%40ffc94789.com <https://groups.google.com/d/msgid/rbase-l/em5c0c0571-c57b-4b66-b148-a7199a992f97%40ffc94789.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/PH0PR11MB5112E3FD8C4C35483AEF63B2C7ACA%40PH0PR11MB5112.namprd11.prod.outlook.com <https://groups.google.com/d/msgid/rbase-l/PH0PR11MB5112E3FD8C4C35483AEF63B2C7ACA%40PH0PR11MB5112.namprd11.prod.outlook.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected] <mailto:[email protected]> . To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/CADAyCN9vsn8zjwMQLk6Nto%3D6sQ%2Brtq95NReALaLp6-kgWr6yLA%40mail.gmail.com <https://groups.google.com/d/msgid/rbase-l/CADAyCN9vsn8zjwMQLk6Nto%3D6sQ%2Brtq95NReALaLp6-kgWr6yLA%40mail.gmail.com?utm_medium=email&utm_source=footer> . -- For group guidelines, visit http://www.rbase.com/support/usersgroup_guidelines.php --- You received this message because you are subscribed to the Google Groups "RBASE-L" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/rbase-l/160b01da15bf%247cf838b0%2476e8aa10%24%40vtgonline.com.
