J-Walk & Associates, Inc.

PUP v6 Worksheet Functions

PUP v6's Worksheet Function Library utility makes it easy to add one or more custom worksheet functions to your workbook. The functions available in the PUP Function Library are listed below. For more information, download the PUP Worksheet Function Help File.

Date & Time Functions

• DAYSINMONTH
  Returns the number of days in the month for a date.
• MONTHWEEK
  Returns the calendar week for a date serial number.
• TIMEXX
  Returns a string that represents a time, displayed as hh:mm:ss:xx, where xx is hundredths of a second (or another unit).
• TIMEXX_ADD
  Returns a string that represents the sum of two TIMEXX time strings. The result is displayed as hh:mm:ss:xx.
• TIMEXX_SUBTRACT
  Returns a string that represents the difference between two TIMEXX time strings. The result is displayed as hh:mm:ss:xx.
• TIMEXX_SUM
  Returns a string that represents the sum TIMEXX time strings. The result is displayed as hh:mm:ss:xx.
• WHICHDAY
  Returns a date that corresponds to a specified day in a month. For example, the date of the first Friday in January for a given year.
• XDATE
  Returns a specified date as text, displayed using the optional fmt date format string. Supports pre-1900 dates.
• XDATEADD
  Returns a date as text, incremented by a specified number of days, using the optional date format string. The days argument can be negative. Supports pre-1900 dates.
• XDATEDAY
  Returns an integer that corresponds to the day for a date. Supports pre-1900 dates.
• XDATEDIF
  Returns the number of days between two dates. Supports pre-1900 dates.
• XDATEDOW
  Returns an integer that corresponds to the day of the week for a date. Supports pre-1900 dates.
• XDATEMONTH
  Returns an integer (between 1 and 12) that corresponds to the month for a date. Supports pre-1900 dates.
• XDATEYEAR
  Returns the year for a date. Supports pre-1900 dates.
• XDATEYEARDIF
  Returns the number of full years between two dates. This function is useful for calculating ages. Supports pre-1900 dates.

Information Functions

• CELLCOLORINDEX
  Returns a number that corresponds to the cell's background color index.
• CELLFONTCOLOR
  Returns a number that corresponds to the cell's font color index.
• CELLHASFORMULA
  Returns TRUE if cell has a formula.
• CELLTYPE
  Returns the cell type of the upper left cell in a range.
• EXCELDIR
  Displays the full path for the folder in which Excel is installed.
• FILEEXISTS
  Returns TRUE if the specified file exists; FALSE otherwise.
• FILENAME
  Displays the full path and file name of the workbook.
• NUMBERFORMAT
  Returns a string that represents the cell's number format.
• SHEETCOUNT
  Returns the number of sheets in the workbook.
• SHEETNAME
  Returns the name of a worksheet.
• USER
  Returns the name of the current user.

Lookup & Reference Functions

• CREDITCARD
  Accepts a credit card number and returns a string that represents the type of credit card. .
• HINTERPOLATE
  Similar to Excel's HLOOKUP function, but it returns an interpolated value if an exact match is not found.
• LASTINCOLUMN
  Returns the value in the last non-empty cell in a column.
• LASTINROW
  Returns the value in the last non-empty cell in a row.
• SHEETOFFSET
  Allows relative sheet referencing.
• UNIQUEITEMS
  Returns the number of unique items in a list. Or, returns an array consisting of the unique items in a list.
• VINTERPOLATE
  Similar to Excel's VLOOKUP function, but it returns an interpolated value if an exact match is not found.

Statistical & Math Functions

• COUNTAVISIBLE
  Similar to Excel's COUNTA function, but it returns the count of just the visible cells. Excel's built-in SUBTOTAL function is a better choice.
• COUNTBETWEEN
  Counts the number of cells that contain a value between two values.
• MAXALLSHEETS
  Returns the maximum value in a particular cell across all worksheets in a workbook.
• MINALLSHEETS
  Returns the minimum value in a particular cell across all worksheets in a workbook.
• SELECTONE
  Returns the value in a cell chosen at random from a range.
• STATICRAND
  Returns a random number that doesn't change when the worksheet is recalculated.
• SUMALLSHEETS
  Returns the sum of all values in a particular cell across all worksheets in a workbook.
• SUMVISIBLE
  Similar to Excel's SUM function, but it returns the SUM of just the visible cells. Excel's built-in SUBTOTAL function is a better choice.
• TOPAVERAGE
  Returns the average of the top n values in a list.

Text Functions

• CONTAINS
  Returns TRUE if a string contains a specified sub-string.
• DOLLARTEXT
  Returns its numeric argument, spelled out as dollars and cents text.
• EXTRACTELEMENT
  Returns the nth element of a text string, where the elements are separated by a specified separator character.
• INSERTSTRING
  Inserts a text string at a specified location within another string.
• ISLIKE
  Returns TRUE if a string matches a pattern.
• PARSENAME
  Splits a string that contains a name into its component parts: first name, middle name, and last name.
• REMOVESPACES
  Removes all of the spaces from a string.
• SCRAMBLE
  Accepts text or a value and returns it scrambled randomly.