All things TrackVia!

Read, write, comment.

This is a public Category  publicRSS

Archived FAQ

    How do I use date formulas?
    Archived FAQ posted 12/19/08 by Admin , tagged Field Types
    892 Views
    question:
    How do I use date formulas?
    answer:

    Date and date-and-time fields are represented internally inside TrackVia as text, in a YYYY-MM-DD HH:MM:SS format. That means that if you use a date field inside a calculated short answer field, like

    concatenate(name, " was born on ", DOB)

    the formula will return text that looks like

    Joe Montana was born on 1956-06-11 12:00:00

    To snip out the time part, you might want to use the built-in mid function (see the article on Text Formulas) like

    concatenate(name, " was born on ", mid(DOB,1,10))

    which produces

    Joe Montana was born on 1956-06-11

    However, if your formula is part of a calculated date field, the date will display like Jun 11, 1956, just like any other date field. It will have the same display options, sorting behavior, and other characteristics of a non-calculated date field.

    There are two built-in date fields you can make use of in your formulas: Created Date and Updated Date. Enter them in formulas just like you would any other field name.

    When specifying fixed dates in your formula, put them in YYYY-MM-DD format, surrounded by quotation marks. A formula to calculate how many days late someone's taxes were filed (in the U.S., that is) would be

    datedif("2007-04-15", date filed)

    Built-in functions. TrackVia's built-in date functions are shown below. The quantities in parentheses are inputs that the function requires. In your formulas, these should be database field names or fixed dates inside quotation marks.

    Function Description
    dateadd(date, N) Adds N days to date, that is, returns the date N days after the input date. If N is negative, it will return the date N days before the input date.

    Example: dateadd("2007-06-25", 14) will produce 2007-07-09 in a calculated short answer field, and Jul 9, 2007 in a calculated date field.
    datesub(date, N) Subtracts N days from date, that is, returns the date N days before the input date. If N is negative, it will return the date N days after the input date.
    datedif(date1, date2) Returns the number of days between date1 and date2. If date2 comes before date1, it returns a negative number.

    Example: datedif("2007-06-25", "2007-07-09") returns 14, because there are 14 days between June 25 and July 9.
    date(year, month, day) Constructs a date value from the number of the year, month, and day. The date value can be used in calculated short answer fields, where it will appear in YYYY-MM-DD format, or date fields, where it will appear like Aug 3, 2006.

    Example: date(2006, 8, 3) returns Thu Aug 3, 2006 if used in a calculated date field.
    year(date)
    month(date)
    day(date)    		 Returns the year part of a date, as a number.
    Returns the month part of a date, as a number.
    Returns the day part of a date, as a number.

    Example: month("2007-05-12") returns 5.