Formula - Date

A Formula - Date field will allow you to create a formula and have the end result formatted as a date field.    You can only use Date Selection fields in the formula. This guide will provide some commonly used formulas.

Note: You can copy and paste any one of these formulas into the formula box.  Drag and drop your numeric fields to replace "field".  You can also put numeric values in place of fields.



• Today's Date
  
  This will return Today's date for the current day formatted as a number:
  
  TODAY()
  
  
  
• Today's Date - Formatted
  
  This will return Today's date for the current day formatted:
  
  TEXT(Today(),"mm/dd/yyy hh:mm:ss tt")
  
  You can delete the characters if you do not need the specifics (mm/dd/yyyy hh:mm:ss tt)
  
  
  
• Age
  
  This will return the number of days since a specific date formatted as number:
  
  ROUNDDOWN((today()-datevalue(¦birthdatex¦))/365
  
  
  
• Date - specific number of days after a date field
  
  This will return a date given a date field and specific number of days after the date field:
  
  DATEVALUE(¦closedate¦)+#
  
  Replace # with the specific number of days you would like to return the date for.  Replace "+" with "-" for specific number of days before instead of after.
  
  
  
• Day
  
  Returns the day of the month, a number from 1 to 31:
  
  DAY(¦invoicedue¦)
  
  
  
• Hour
  
  This will return the hour as a number from 0 (12 am) to 23 (11 pm):
  
  HOUR(¦addeddate¦)
  
  
  
• Month
  
  This will return the month as a number from 1 (Jan) to 12 (Dec):
  
  MONTH(¦updateddate¦)
  
  
  
• Number of Work Days Between Dates
  
  This will return the number of work days between two date fields:
  
  NETWORKDAYS(¦billstartdate¦,¦billenddate¦)
  
  
  
• Number of Days between two dates
  
  This will return the number of days between two date fields:
  
  DATEVALUE(¦billenddate¦)-DATEVALUE(¦billstartdate¦)
  
  
  
• Year
  
  This will return the year from a date field:
  
  YEAR(¦closedate¦)
  
  
  
• Week Number
  
  This will return the week number in the year:
  
  WEEKNUM(serialnumber,¦closedate¦)
  
  Where "serialnumber" is a number 1-7 (1 = week starts on Sunday, 2 = week starts on Monday...)
  
  
  
• Number of Years In Between Two dates
  
  This will return the number of years in between two date fields:
  
  YEARFRAC(¦customerstartdate¦,¦today¦)