Date Calculations and Formula

Some of the frequently asked questions on Date Calculations and Formula are listed below:

  1. How can I add days to a date field
  2. How can I calculate the age of a person from a given Date of Birth
  3. Is it possible to obtain month and day information from a Date field?
  4. I would like to order the DOB field in my form, based on month and day. How can I do this?
  5. I have a form to enter my patient details. I want to create a unique code for each patient based on their DOB and name combination. Can I do this?
  6. How can I calculate the number of days between two date fields?
  7. How can I calculate the number of hours between two date and time fields?
  8. I want to calculate the sum of all the numeric fields in my form. How can I do this?
  9. I have three numeric fields to enter the scores of my agents in each category. I would like to calculate the sum of all the scores and display it as a percentage of the total score. How can I do this?
  10. I want to write a script that will assemble a url from three fields - street, city and state. What's the best way to do this?
  11. I am using a form as a book inventory loader with fields like author, title, isbn etc. I wish to create a URL to Amazon based on information entered into the form, for example,
    "http://www.amazon.co.uk/exec/obidos/ASIN/" & [ISBN]

 

1. How can I add days to a date field

You can manipulate a date field by using the '+' (to add) and '-' (to subtract) Operators or by using the Built-in date functions. For example, if you would like to add 364 days to a particular date field, say StartDate and display it in another field, define a formula field named ExpiryDate with expression (StartDate + '52W:0D:0H:0M:0S') or by using the addDay built-in date function, StartDate.addDay(364).

For example, 

form  Sample
{
    
    StartDate
    (
        type  =  date
    )


    ExpiryDate
    (
        type  =  formula
        value  =  (StartDate  +  '52W:0D:0H:0M:0S')
    )
}

where,

- 'W' stands for the number of weeks.
- 'D' stands for the number of days.
-  The other units of time supported are 'H' (hours), 'M' (minutes) and 'S' (seconds).

2. How can I calculate the age of a person from a given Date of Birth?

To calculate the age of a person from a give DOB, add formula field to calculate Age and specify the formula expression as
((zoho.currentdate - DOB) / (1000 * 60 * 60 * 24 * 365)).round(0)

form  Sample
{
    
    DOB
    (
        type  =  date
    )


    Age
    (
        type  =  formula
        value  =  ((zoho.currentdate - DOB)  /  (1000  *  60  *  60  *  24  *  365)).round(0)
    )
}

3. Is it possible to obtain month and day information from a given date field?

Yes, you can use the Deluge date function getDay() and getMonth() to obtain the day and month information.

- getDay() returns the day of the month for a particular year. The value returned is between 1 and 31.
- getMonth() returns the month of the year. The value returned is between 1 and 12.

Usage:

<date>.getDay()
<date>.getMonth()

where, <date> is a form field of type "date"

Refer Deluge Reference - Built-in Functions - Date Functions for more information on the supported Deluge date functions.

 

4. I would like to order the DOB field in my form, based on month and day. How can I do this?

To order the DOB field by month and day, add a formula field with expression,

((((((DOB.getMonth() / 100).round(2))) + "").getSuffix(".")) + "-") + ((((DOB.getDay() / 100).round(2))) + "").getSuffix(".")

where,

- DOB.getMonth, returns a number in the range (1 -12), representing the number of the month of the year, on which the date occurs.
- DOB.getDay(), returns a number in the range (1 - 31), representing the number of the day of the month on which the date occurs.

The formula expression specified in the Order_DOB field, returns: [mm]-[dd]. It makes both the month and date, a two digit number. Otherwise ordering goes wrong like: 1, 10, 11, 12, 2, 3, etc. 

form  Sample
{
    
    DOB
    (
        type  =  date
    )


    Age
    (
        type  =  formula
        value  =  ((zoho.currentdate - DOB)  /  (1000  *  60  *  60  *  24  *  365)).round(0)
    )

    Order_DOB
    (
        type  =  formula
        value  =  ((((((DOB.getMonth()  /  100).round(2))) + "").getSuffix(".")) + "-") + ((((DOB.getDay()  /  100)
                  .round(2))) + "").getSuffix(".")
    )
}

5. I have a form to enter my patient details. I want to create a unique code for each patient based on their DOB and Name combination. Can I do this?

To create a unique code based on the Date of Birth (DOB) and Name combination, add a Formula field with formula expression as

((((DOB.getMonth())) + Name) + (DOB.getDay())) + (DOB.getYear())

where,

- DOB.getMonth, returns a number in the range (1 -12), representing the number of the month of the year, on which the date occurs.
- DOB.getDay(), returns a number in the range (1 - 31), representing the number of the day of the month on which the date occurs.
- DOB.getYear(), returns a number representing the year of the date.

For example, if DOB is specified as 07-Sep-1994, with Name as "Henry", the formula expression specified in the Code field, returns 9henry71994

form  Sample
{    
    Name
    (
        type  =  text
    )

    DOB
    (
        type  =  date
    )

    Age
    (
        type  =  formula
        value  =  ((zoho.currentdate - DOB)  /  (1000  *  60  *  60  *  24  *  365)).round(0)
    )

    Order_DOB
    (
        type  =  formula
        value  =  ((((((DOB.getMonth()/100).round(2))) + "").getSuffix(".")) + "-") 
+ ((((DOB.getDay()/100).round(2))) + "").getSuffix(".")
    )


    Code
    (
        type  =  formula

        value  =  ((((DOB.getMonth())) + Name) + (DOB.getDay())) + (DOB.getYear())
    )
}

6. How can I calculate the number of days between two date fields?

To calculate the number of days between two given date fields, for instance, ServiceStartDate and ServiceEndDate, add a formula field with formula expression as ((ServiceEndDate - ServiceStartDate) / (1000 * 60 * 60 * 24)).

 
       ServiceStartDate
        (
            displayname  =  "Service Start Date"
            type  =  date
        )

        ServiceEndDate
        (
            displayname  =  "Service End Date"
            type  =  date
        )

        Formula_1
        (
            type  =  formula
            value  =  ((ServiceEndDate - ServiceStartDate)  /  (1000  *  60  *  60  *  24))
        )

7. How can I calculate the number of hours between two date and time fields?

To calculate the number of hours between two date and time fields, add a formula field and specify the formula expression as ((To_Date - From_Date) / (1000 * 60 * 60)) 

form  Sample
{    
    FromDate
    (
        type  =  datetime
    )

    ToDate
    (
        type  =  datetime
    )

    Hours
    (
        type  =  formula
        value  =  ((To_Date - From_Date) / (1000 * 60 * 60))
    )
}

8. I want to calculate the sum of all the numeric fields in my form. How can I do this?

To find the sum of numeric field values in a form, add a formula field with the expression (field1 + field2 + field3), where, field1, field2 and field3 are field names in a form. The script definition of the form "Semester Marks" is given below. This form contains number fields to store the marks obtained in English, Maths and Science. The field "Total Marks" and "Average" are formula fields to find the total and average marks obtained.

form  Semester_Marks
{
    displayname  =  "Semester Marks"
    
    English
    (
        type  =  number
    )

    Maths
    (
        type  =  number
    )

    Science
    (
        type  =  number
        width  =  20
    )

    TotalMarks
    (
        type  =  formula
        value  =  (Maths  +  English  +  Science)
    )

    Average
    (
        type  =  formula
        value  =  ((English  +  Maths  +  Science)  /  3)
    )

}

9. I have three numeric fields to enter the scores of my agents in each category. I would like to calculate the sum of all the scores and display it as a percentage of the total score. How can I do this?

To find the sum of the scores and display it as a percentage of the total score, add a formula field with the expression ((score1 + score2 + score3) * 100 / 50).round(2), where, score1, score2, score3 are numeric field names in a form. The script definition of the form "AgentScore" is given below. The field "ScorePercent" is a formula fields to find the percentage score.

form  Score 
{
    displayname  =  "Agent Score"
    
    Score1
    (
        type  =  number
    )

    Score2
    (
        type  =  number
    )

    Score3
    (
        type  =  number
        width  =  20
    )

    ScorePercent
    (
        type  =  formula
        value  = ((score1  +  score2  +  score3)  *  100  /  50).round(2)
    )

   
}

10. I want to write a script that will assemble a url from three fields -street, city and state. What's the best way to do this?

To create a url from the value of three fields namely Street, City and State, add a formula field with the given expression, where, Street, City and State are field names in the form.

"<a href='http://" + Street + "/" + City + "/" + State + ".html' target='_blank'> " + " http://" + Street + "/" + City + "/" + State + "</a>"

11. I am using a form as a book inventory loader with fields like author, title, isbn etc. I wish to create a URL to Amazon based on information entered into the form, for example,
"http://www.amazon.co.uk/exec/obidos/ASIN/" & [ISBN]

To create a url to Amazon based on the form fields, add a formula field with the given expression, where ISBN is the name of the field in your form.

"<a href = 'http://www.amazon.co.uk/exec/obidos/ASIN/" + ISBN + "'target=_blank>Link to Amazon</a>"

Now, in the View, the value "Link to Amazon" will show up in the Formula field column for every record added which will forward to the corresponding url. Also while in the Edit mode of your application, click on Application Settings from the top right side and choose the XSS Settings to LOW and Save.