date value
What Does It Do?
The function is used to convert a piece of text into a date which can be used in calculations. Dates expressed as text are often created when data is imported from other programs, such as
exports from mainframe computers.
Syntax
=DATEVALUE(text)
Formatting
The result will normally be shown as a number which represents the date. This number can be formatted to any of the normal date formats by using Format,Cells,Number,Date.
| Date | Date Value | |
| 25-dec-99 | 36519 | =DATEVALUE(C4) |
| 25/12/99 | #VALUE! | =DATEVALUE(C5) |
| 25-dec-99 | 36519 | =DATEVALUE(C6) |
| 25/12/99 | #VALUE! | =DATEVALUE(C7) |
Example:
The example uses the =DATEVALUE and the =TODAY functions to calculate the number of days remaining on a property lease.
The =DATEVALUE function was used because the date has been entered in the cell as a piece of text, probably after being imported from an external program.
Table 1:
| Property Ref. | Expiry Date | Days Until Expiry |
| BC100 | 25-dec-99 | -7347 |
| FG700 | 10-july/99 | -7515 |
| TD200 | 13-sep-98 | -7815 |
| HJ900 | 30/5/2000 | #VALUE! |
| =DATEVALUE(E32)-TODAY() |