Take field, subtract date, to get age

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!
LVL 6
TheRookie32Asked:
Who is Participating?
 
marilyngCommented:

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:

    TD:=@TextToTime("Today");
   @Round(((@Year(TD)-@Year(Birthday))*12 +(@Month(TD)-@Month(Birthday)))/12;.2)
---------
CHOICE 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:
  @SetField("todayDate";@Today);
   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.

0
 
marilyngCommented:
Sure,

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.

CurrentDate:=@Today;
ThisDate:=YourFieldName;
@if(thisDate !="";(CurrentDate - ThisDate) / 86400 + 1;"NA")
0
 
Sjef BosmanGroupware ConsultantCommented:
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...
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
TheRookie32Author Commented:
@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...
0
 
marilyngCommented:
:)).. where did birthday come from?  Got a snippet for that, too, somewhere... give me a moment to find it.
0
 
Sjef BosmanGroupware ConsultantCommented:
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);
    age
0
 
marilyngCommented:
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.  

0
 
Sjef BosmanGroupware ConsultantCommented:
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"... ;)
0
 
marilyngCommented:
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.
BD:=Birthday;
TD:=@Today;
@If(BD="";@Return("");"");
@Integer(@If(TD<@Date(@Year(TD);@Month(BD);@Day(BD));@Year(TD)-@Year(BD)-1;@Year(TD)-@Year(BD)))

0
 
Sjef BosmanGroupware ConsultantCommented:
bd, db, dbbd, bdbd, bbbbb *grunt*
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.