This function converts a text string into a valid date (in other words, a date serial number). The text string must contain data that is recognizable as a date. The function recognizes text entered in any of the date formats shown in the Number tab in the Format Cells dialog box, when you choose the Date category. You can specify any date from January 1, 1900 to February 6, 2040. The format of a DATEVALUE function is
An example of the DATEVALUE function and its result is
= DATEVALUE (B5), where B5 contains 1/25/91
The result, 31801, is the number of days between 1/1/00 and 1/25/91.
These functions return the day, month, or year corresponding to a specified date. The date variable can be any valid date entered as the number of days elapsed since January 1, 1900. The value can also be a reference to a cell containing a valid date, or it can be any date expression resulting in a valid date. These functions break a date into its various portions. The following syntax is used for the DAY, MONTH, and YEAR functions:
Suppose cell B2 contains the date 2/24/91. Here are some examples of these functions and their results:
The NOW function pulls the current date and time from the DOS startup date and time. The value is returned as a number with a decimal value, as in 2245.2025. The integer part of this number represents the date (in "days elapsed" format) and the fractional part represents the time (in "time elapsed" format). The NOW function's format is
There is no argument for the function. You can simply format the value into a date using any of the date formats in the Number tab in the Format Cells dialog box, when you choose the Date category. Or you can format the value into a time using the time formats in the Number tab in the Format Cells dialog box, when you choose the Time category. You can also use the TRUNC function to separate the two portions, as in =TRUNC(NOW( )). This formula strips off the decimal portion of the date/time serial number, turning it into a date only. You can then format this date or use the DAY, MONTH, and YEAR functions to split the value even further.
The TIMEVALUE function converts a text string into a valid time. The text string must be recognizable as a time entry. It should resemble any of the time formats in the Format Number command. The result is displayed in the "time elapsed" format but can be formatted with the Format Number command. Enter times as text strings by typing them in two or three parts (for example, 12:30 or 12:30:15) or by including the AM/PM (for example, 12:30:00 AM). The format for the TIMEVALUE function is
The following is an example of the function and its result:
=TIMEVALUE(B4), where B4 0.9828125contains '11:35:15 PM'
The numerical result can be formatted as the valid time 11:35:15 PM.
This function converts a serial number to a day of the week as a value from 1 to 7. The value 1 equals Sunday, 2 equals Monday, and so on. To format this value as the appropriate day name, use the Format Cells command and, on the Number tab, specify the custom date format "dddd" by choosing the Custom category to create the format.
The syntax for the WEEKDAY function looks like this:
An example of this function and its result is
The 6 can be formatted as the weekday name Thursday using the "dddd" format.