Print

[dmwebb]

Is there anywhere that has information on creating and using calculations?  The manual has 2 pages of "information," none of which is relevant.

I have a <DateStarted> field and would like to calculate number of years of experience from that field, e.g.,

Integer[<CurrentDate>-<DateStarted>]

I have no idea how to get the system date nor the appropriate syntax for the equation.
Anyone have any experience they can share?
Thanks,
DW

[Forum Administrator]

The easiest way to accomplish this is to do it in your database or Excel spreadsheet before importing. Create an Experience field that calculates the number of years based on the current date and the start date. I've included some example Excel formulas for you to look at.

If, for instance, your StartDate field is A1 and your CurrentDate field is B2, here are some formulas you could use to calculate the time between two dates:

To calculate the number of days, months, and years:
=YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH
(B1)=MONTH(A1),DAY(B1)<DAY(A1))),1,0)&" years, "&MONTH
(B1)-MONTH(A1)+IF(AND(MONTH(B1)<=MONTH(A1),DAY(B1)<DAY
(A1)),11,IF(AND(MONTH(B1)<MONTH(A1),DAY(B1)>=DAY(A1)),
12,IF(AND(MONTH(B1)>MONTH(A1),DAY(B1)<DAY(A1)),-1)))
&" months, "&B1-DATE(YEAR(B1),MONTH(B1)-IF(DAY(B1)<DAY
(A1),1,0),DAY(A1))&" days"

To calculate the number of whole years:
=YEAR(B1)-YEAR(A1)-IF(OR(MONTH(B1)<MONTH(A1),AND(MONTH(B1)=MONTH(A1 ),DAY(B1)<DAY(A1))),1,0)&" years"

With OrgPlus' ability to refresh, you can keep your data in your database (or spreadsheet), making any changes as necessary and telling OrgPlus to refresh the data.

The formula functions in OrgPlus are designed primarily for number fields and making a number field house a date would cause your calculations to be inaccurate.

I hope this helped. If you have any other questions, please don't hesitate to ask.

Sincerely,

Trisha Diesch
The OrgPlus Support Team
OrgPlus. Organize. Visualize. Strategize

HumanConcepts
www.orgplus.com