Take field, subtract date, to get age

Posted on 2006-03-21
Medium Priority
Last Modified: 2013-12-18
I would like to have a computed field(?), that will take the value that is entered in a date time field and subtract that from the current date, to display in the computed field.  Is this possible or even the way notes is supposed to work?  What does the code look like?  Thanks!
Question by:TheRookie32
  • 5
  • 4
LVL 18

Expert Comment

ID: 16249605

Link to a couple of variations: http://www.experts-exchange.com/Applications/Email/Lotus_Notes_Domino/Q_21711859.html?query=difference+between+days&topics=133

The formula for the computed Field - basically subtracting dates leaves you with the difference in seconds, so you divide the difference by 24*60*60, also in order not to throw an error, you should check if your field date is not empty.

@if(thisDate !="";(CurrentDate - ThisDate) / 86400 + 1;"NA")
LVL 46

Expert Comment

by:Sjef Bosman
ID: 16250763
Marilyn, do you think the following is possible:
    bd:= Birthday;
    age:= @Adjust(@Today; -@Year(bd);-@Month(bd);-@Day(db);0;0;0)

Nope, won't do it correctly either, because of the leap years.

Another one:
    bd:= Birthday;
    td:= @Today;
    ndays:= (td - bd) / 86400;
    nyears:= @Integer(ndays/365);
    age:= @If(@Adjust(bd; nyears+1;0;0;0;0;0)<=@Year(td); nyears+1; nyears)

You get the drift...

Author Comment

ID: 16252903
@Marilyng : how do i get that age to display in a view?  Do i need to not use a computed field?  Sorry... its like my 3rd day using Notes from a (limited) .Net background...
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 18

Expert Comment

ID: 16252989
:)).. where did birthday come from?  Got a snippet for that, too, somewhere... give me a moment to find it.
LVL 18

Accepted Solution

marilyng earned 800 total points
ID: 16253615

The birthday formula - yy.mm, put a field for birthday on your form, computed for display, so it updates each time the form opens.

@Round(((@Year(@Today)-@Year(Birthday))*12 +(@Month(@Today)-@Month(Birthday)))/12;.2)

If you want to do this in views, well, you have three choices:
Choice 1:  Set the view refresh indexes to at least once a day in the view properties,
Make sure you fill in your Birthday Field Name;
PUt this in the Column Formula:

   @Round(((@Year(TD)-@Year(Birthday))*12 +(@Month(TD)-@Month(Birthday)))/12;.2)
Leave the Formula:
    @Round(((@Year(@Today)-@Year(Birthday))*12 +(@Month(@Today)-@Month(Birthday)))/12;.2)

Knowing that the twistee for the view will cause the server to refresh the view every second.

Choice 3:
Create an agent that will run every night and stamp all birthday form field: todayDate with today's date,
Create a field on your form: todayDate, computed, with the default formula = todayDate.
Change your view formula to:
     @Round(((@Year(todayDate)-@Year(Birthday))*12 +(@Month(todayDate)-@Month(Birthday)))/12;.2)

The agent, of course, has two lines in the formula:
   SELECT @All

Set a condition to run on your form, and schedule it to run on the server, shortly after midnight.  Make sure to save the agent with an ID that has permission to run agents on the server.

LVL 46

Assisted Solution

by:Sjef Bosman
Sjef Bosman earned 200 total points
ID: 16255235
The form should have a field Birthday and then you follow Marilyn's post above, with one of the three options.

However, I think the formula to compute someone's age is not correct, you also need the day. The column formula:

    bd:= Birthday;
    td:= @Today;
    bd1:= @Year(bd)*10000 + @Month(db)*100 + @Day(bd);
    td1:= @Year(td)*10000 + @Month(td)*100 + @Day(td);
    age:= @Integer((td1-db1)/10000);
LVL 18

Expert Comment

ID: 16259626
Really?  Why is it incorrect?  Most times, at least from HR perspective, they only want the month.

The correct posting would be to say, if you want a formula to the day, then use sjef's post.

If you need a formula to reflect the yy.mm then use Marilyn's post.

It would be correct to say a formula is incorrect if it didn't work,

But since we have a generic "age" specified, with no parameters, a formula to show year, or year +months, or Year + months + days would be correct, as long as the calculation specified is the result you get.  

LVL 46

Expert Comment

by:Sjef Bosman
ID: 16259874
If I were born in 1966, on March 24, wouldn't your formula make me 40 already? And I've been searching all over, but I can't find my generic "age"... ;)
LVL 18

Expert Comment

ID: 16263805
I did say that the formula I posted would return yy.mm  or age + month:)  (HR likes to make eligible on the birthmonth, not the birthday)

LOL, you have typo's in your code: @Year(bd)*10000 + @Month(>>db<<)*100 + @Day(bd);@Integer((td1->>db1<<), when you paste yours into a field and try to run, you get error messages about expecting a number:))

However, here is another way that includes the DAY :) yuk,yuk.. and no typo's.

LVL 46

Expert Comment

by:Sjef Bosman
ID: 16266766
bd, db, dbbd, bdbd, bbbbb *grunt*

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You’ve got a lotus Domino web server, and you have been told that “leverage browser caching” is a must do. This means that we have to tell the browser everywhere in the web to use cache. In other words, we set (and send) an expiration date in the HT…
For beginners of Lotus Notes user this is important to know about the types of files and their location supported by IBM Notes. Mostly users are unaware about how many file types are created and what their usages are. This Article is fully dedicated…
Integration Management Part 2
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…
Suggested Courses
Course of the Month15 days, 6 hours left to enroll

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question