Functions


Function - CDate (expression)

CDATE - Returns the expression converted to a date data type (Date).


The string must be formatted according to the WRS and use the windows langauge for month names.
This function doesn't recognise days of the week.
If the year is not specified then the current year is used.
This function does not recognise the % wildcard character that represents any character.


Function - DateSerial(year, month, day)

DATESERIAL - Returns the date given a year, month and day (Date).



Function - DateValue(date)

DATEVALUE - Returns the date given a string representation of a date (Date).


This function does not recognise days of the week.
If the year is not specified then the current year is used.
This function converts a String to a Date datatype
The string must be formatted according to the WRS and use the windows language for month names.


Function - DateValueToUS (User Defined)


Public Function DateValueToUs(ByVal sDate As String) As Date 
   = Application.Evaluate("DATEVALUE("'" & sDate & ")")
End Function


Function - Format (Dates)

FORMAT - Returns the text string of a number or date in a particular format (String).
This function converts a date (or a number) to a string
If you don't provide a number format then this function behaves exactly the same as CSTR
Any date format must use US date symbols (d,m,y) although the result is formatted according to the WRS for date separators and the WRS language for weekday and month names.


Format(DateSerial(2006,1,1),"dddd dd-mm-yy") = "-----" in UK 
Format(DateSerial(2006,1,1),"dddd dd-mm-yy") = "-----" in US
Format(DateSerial(2006,1,1),"dddd dd-mm-yy") = "----" in ????
Format(Date(), "Short date") =
Format(Date(), "Medium date") =
Format(Date(), "long date") =

Function - Format (Numbers)

FORMAT - Returns the text string of a number or date in a particular format (String).
This function converts a number (or date) to a string
If the number format is left blank, this behaves in exactly the same way as CStr (ignore the online help which says Str)
Format(True) = "True" (always in English)
This function does not return a date order to correspond to your WRS settings.
You should check the date order before using this function.


Function - FormatCurrency, FormatDateTime, FormatNumber, FormatPercent

FORMATCURRENCY - Returns the expression formatted as a currency value.
FORMATDATETIME - Returns the expression formatted as a date or time.
FORMATNUMBER - Returns the expression formatted as a number.
FORMATPERCENT - Returns the expression formatted as a percentage.
These functions provides the same functionality as the Format function but uses parameters to define the specific resulting format instead of a custom format string.
They correspond to the standard options on the (Format > Cells)(Number tab).
While the Format function corresponds to the custom number format.


Function - Str

STR - Returns the text string of a number (String).
This function converts a number, date or boolean into a US formatted string regardless of your WRS or office language or windows language.
When converting a positive number it adds a space on the left
When converting a decimal it does not add a leading zero.


Function - Val(string)

VAL - Returns the numbers contained in a string as a numeric value of the appropriate datatype (?).
This function does not recognise US-formatted date strings
This function converts strings to numbers and can only convert US-formatted numerical strings to numbers.
This function unlike all the others does not.



© 2019 Better Solutions Limited. All Rights Reserved. © 2019 Better Solutions Limited TopPrevNext