This function returns the current date at the time it is called.
DATE()
Rule | Meaning |
|---|---|
| DATE() | If this function is called on the 12th August 2009 then it would return the value: "12/08/2009". The format the date
is returned will depend on the system settings of the computer
performing the calculation. |
This function returns the system date value for the given date.
DATEVALUE()
Rule | Meaning |
|---|---|
| DATEVALUE("09/21/2010") | This will return the system value 40442 for the given date |
| DATEVALUE(DATE()) | If this function is called on the 11th October 2010 then it would return the value: 40462. |
This function returns the day number from the given value
DAY()
Rule | Meaning |
|---|---|
| DAY() | If this function is called on the 11th October 2010 then it would return the value: 11. |
| DAY(40462) | This will return 11 from the given system date value |
| DAY("09/21/2010") | This will return 21 from the given date |
This function returns the hour, a number from 0 to 23, from the given value.
HOUR()
Rule | Meaning |
|---|---|
| HOUR(NOW()) | If this function is called at the time of 13.20 then it would return the value: 13. |
| HOUR(0.555) | This will return 13 from the given system time value |
This function returns the minute, a number from 0 to 59, from the given value.
MINUTE()
Rule | Meaning |
|---|---|
| MINUTE(NOW()) | If this function is called at the time of 13.20 then it would return the value: 20. |
| MINUTE("16:19:22") | This will return 19 from the given time value |
This function returns the month number from the given value
MONTH()
Rule | Meaning |
|---|---|
| MONTH(DATE()) | If this function is called on the 12th August 2009 then it would return the value: 8 |
| MONTH("11 OCTOBER 2010") | This will return 10 from the given date. |
This function returns the second, a number from 0 to 59, from the given value.
SECOND()
Rule | Meaning |
|---|---|
| SECOND(NOW()) | If this function is called at the time of 13.20:47 then it would return the value: 47. |
| SECOND("16:28:10") | This will return 10 from the given time value |
This function converts the given time to a serial number value
TIMEVALUE()
Rule | Meaning |
|---|---|
| TIMEVALUE(NOW()) | If this function is called at the time of 13.17:02 it will return the system value 0.553496458334848. |
| TIMEVALUE("16:25:21") | This will return the system value 0.684270833 for the given time. |
This function returns the number of work days between the given dates
NETWORKDAYS([StartDate],[EndDate],[Holidays])
StartDate is the date to start counting the work days from. Date format must be month/date/year.
EndDate is the date to count the work days to. Date format must be month/date/year.
Holidays are the dates to exclude from the returned number of work days.. Date format must be month/date/year.
Rule | Meaning |
|---|---|
| NETWORKDAYS("10/08/2010","10/30/2010","10/12/2010") | This will count all the work days in between 10/08/2010 and 10/30/2010, but exclude the day of 10/12/2010 and will return 15 |
| NETWORKDAYS("10/08/2010","10/30/2010","10/12/2010","10/13/2010") | This will count all the work days in between 10/08/2010 and 10/30/2010, but exclude the days of 10/12/2010 and 10/13/2010 and will return 14 |
This function returns the number of work days between the given dates, allowances for which days are weekend days and holidays are
NETWORKDAYSCUSTOM([StartDate],[EndDate],[WeekendValue],[Holidays])
StartDate is the date to start counting the work days from. Date format must be month/date/year.
EndDate is the date to count the work days to. Date format must be month/date/year.
WeekendValue is a number (from the chart below) that represents the days to count as weekend days
Holidays are the dates to exclude from the returned number of work days.. Date format must be month/date/year.
Weekend Value | Weekend Days |
|---|---|
| 1 or Omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
Holidays
Rule | Meaning |
|---|---|
| NETWORKDAYSCUSTOM("10/08/2010","10/30/2010",3,"10/12/2010") | This will count all the work days in between 10/08/2010 and 10/30/2010, taking Monday and Tuesdays as weekend days (3), but exclude the day of 10/12/2010 and will return 17 |
This function returns a number representing the day of the week.
WEEKDAY([Date],[Return Type])
Date is the date to return the weekday from. Date format must be month/date/year.
Return Type is a number representing the day in which the week starts
Return Type | Number returned |
|---|---|
| 1 or Omitted | Numbers 1 (Sunday) through 7 (Saturday) |
| 2 | Numbers 1 (Monday) through 7 (Sunday) |
| 3 | Numbers 0 (Monday) through 6 (Sunday) |
| 11 | Numbers 1 (Monday) through 7 (Sunday) |
| 12 | Numbers 1 (Tuesday) through 7 (Monday) |
| 13 | Numbers 1 (Wednesday) through 7 (Tuesday) |
| 14 | Numbers 1 (Thursday) through 7 (Wednesday) |
| 15 | Numbers 1 (Friday) through 7 (Thursday) |
| 16 | Numbers 1 (Saturday) through 7 (Friday) |
| 17 | Numbers 1 (Sunday) through 7 (Saturday) |
Rule | Meaning |
|---|---|
| WEEKDAY(Today(),2) | If this function is called on a Monday it will return 1 as the return type is set to start counting Monday as the first day (2) |
| WEEKDAY("10/22/2010") | This will return 6 as the date given falls on a Friday and the return type omitted which will start counting Sunday as the first day. |
This function returns the week of the given date as a number.
WEEKNUM([Date],[Return Type])
Date is the date to return the week number from. Date format must be month/date/year.
Return Type is a number representing the day on which the week starts
Return Type | Number returned |
|---|---|
| 1 or Omitted | Sunday |
| 2 | Monday |
| 11 | Monday |
| 12 | Tuesday |
| 13 | Wednesday |
| 14 | Thursday |
| 15 | Friday |
| 16 | Saturday |
| 17 | Sunday |
| 21 | Monday |
Rule | Meaning |
|---|---|
| WEEKNUM(Today()) | If this function is called on 10/18/2010 it will return 43 as the week number |
| WEEKNUM("10/18/2010",12) | This will return 42 as the date given falls on a Monday and the week is set to start on a Tuesday (12). |
This function returns the date before or after a specified number of work days
WORKDAY([Date],[Days],[Holidays])
Date is the date to start counting the number of work days from. Date format must be month/date/year.
Days is the number of work days to include
Holidays are the dates to be ignored. When entering a fixed date the format must be Month Day Year.
Rule | Meaning |
|---|---|
| WORKDAY(Today(),10,"10/19/2010") | If this function is called on the 18th October 2010 then it would return the value: "11/02/2010" Note: The format the date is returned will depend on the system settings of the computer performing the calculation. |
This function returns the date before or after a specified number of work days
WORKDAYCUSTOM([Date],[Days],[Weekend],[Holidays])
Date is the date to start counting the number of work days from. Date format must be month/date/year.
Days is the number of work days to include
Weekend is a number that represents the weekend days
Holidays are the dates to be ignored. When entering a fixed date the format must be Month Day Year.
Weekend Value | Weekend Days |
|---|---|
| 1 or Omitted | Saturday, Sunday |
| 2 | Sunday, Monday |
| 3 | Monday, Tuesday |
| 4 | Tuesday, Wednesday |
| 5 | Wednesday, Thursday |
| 6 | Thursday, Friday |
| 7 | Friday, Saturday |
| 11 | Sunday only |
| 12 | Monday only |
| 13 | Tuesday only |
| 14 | Wednesday only |
| 15 | Thursday only |
| 16 | Friday only |
| 17 | Saturday only |
Rule | Meaning |
|---|---|
| WORKDAYCUSTOM(Today(),10,1,"10/27/2010") | If this function is called on the 19th October 2010 then it would return the value: "03/11/2010". Note: The format the date is returned will depend on the system settings of the computer performing the calculation. |
This function returns the year from the given date or date serial number.
YEAR([Date])
Date is the date to return the year from
Rule | Meaning |
|---|---|
| YEAR(43563) | This will return the year 2019 from the given date serial number |
| YEAR(Today()) | If this function is called on the 19th October 2010 then it would return the value: 2010. |
This function returns the largest value in the given table's column which complies with the specified condition.
DMAX([Table or Table Name],[Column Index],[Condition])
Table or Table Name is the table to search in
Column Index is a number representing the index of the column in the table
Condition is the condition to test each cell in the column with
Rule | Meaning |
|---|---|
| DMAX(DwLookupDataTable,1,">10") | This will look in the table DataTable, in column 1 and return the largest value that is less than 10. |
This function returns the smallest value in the given table's column which complies with the specified condition.
DMIN([Table or Table Name],[Column Index],[Condition])
Table or Table Name is the table to search in
Column Index is a number representing the index of the column in the table
Condition is the condition to test each cell in the column with
Rule | Meaning |
|---|---|
| DMIN(DwLookupDataTable,1,"<15") | This will look in the table DataTable, in column 1 and return the smallest value that is greater than 15. |
This function formats one or more values according to the given formatting string.
TEXTFORMAT([Format String], [Values])
Format String is the string to format
Values is the value or collection of values (separated with a comma) to be formatted
Rule | Meaning |
|---|---|
| TEXTFORMAT("C:\Program Files\{0:0.#}\Hello\World\{1:yyyy-MM-dd hh-mm-ss}",9.7, Now()) | Will format the given string to include 9.7 in the first placeholder ({0) and the current date in the second placeholder ({1), formatted as indicated ( :yyyy-MM-dd hh-mm-ss}) |
This function returns the full file path and name of the given file, substituting it's existing file extension with the given extension.
FSCHANGEEXTENSION([Path],[Extension])
Path is the path to the file to change
Extension is the new file extension, or an empty string to remove the file extension
Rule | Meaning |
|---|---|
| FsChangeExtension("C:\DriveWorks 8\New Functions\ChangeFileExtension\ MyFileName","xml") | This will return the value C:\DriveWorks 8\New Functions\ChangeFileExtension\ MyFileName.xml |
This function returns the parent directory path from a given file path
FSGETDIRECTORYNAME([Path])
Path is the path to return the parent directory from
Rule | Meaning |
|---|---|
| FSGETDIRECTORYNAME("C:\DriveWorks 8\Parent Directory\Child Directory") | This will return the path C:\DriveWorks 8\Parent Directory |
This function returns the file name from a file path
FSGETFILENAME([Path])
Path is the path to the file to retrieve the file name from
Rule | Meaning |
|---|---|
| FSGETFILENAME("C:\DriveWorks 8\Parent Directory\Child Directory\ MyFileName.txt") | This will return MyFileName.txt |
This function combines 2 file paths
FSPATHCOMBINE([First Path],[Second Path])
First Path is the first path to combine
Second Path is the second path to combine (must be a relative path)
Rule | Meaning |
|---|---|
| FsPathCombine("C:\DriveWorks 8\New Functions","Child Folder") | This will return the value C:\DriveWorks 8\New Functions\Child Folder |
This function counts the number of items in a list.
LISTCOUNTITEMS([List])
List is the pipe separated list to return the number of items from.
Rule | Meaning |
|---|---|
| LISTCOUNTITEMS("A|B|C|D|E|F") | This will return 6, as the number of items in the list. |
| LISTCOUNTITEMS(DWVariableListItems) | If the result of the variable ListItems is A|B|C|D|E|F this will return 6 |
This function finds the index of an item in a list.
LISTFINDITEM([List],[Item])
List is the pipe separated list to find an item in.
Item is the item to find in a list.
Rule | Meaning |
|---|---|
| LISTFINDITEM("A|B|C|D|E|F","D") | This will return 4 as D is the fourth item in the list |
This function returns the specified item from a list of items
LISTGETITEM([List],[Item Index])
List is the pipe separated list to retrieve an item from.
Item is the index of the item to return.
Rule | Meaning |
|---|---|
| LISTGETITEM("A|B|C|D|E|F",2) | This will return the second (2) item in the list (B) |
This function combines two or more values or lists together into a single list
LISTJOIN([Values or Lists])
Values or Lists are the values or lists to join into a single list
Rule | Meaning |
|---|---|
| LISTJOIN("A|B|C","D|E|F") | This will return A|B|C|D|E|F |