DEFINITION :- The Excel DATEVALUE Function Converts A Date Stored As Text In A Cell Into A Valid Excel Date. A Valid Excel Dates Are More Useful Than Text Dates As They Can Be Referenced And Can Be Used With Formulas And Pivot Tables To Perform Date-Based Reports Or Analysis.
Syntax:- =DATEVALUE(Valid Date As Text Format)
(1) If Date As Text Is Entered Directly Into The Formula It Must Be In Double Quotes . Or If Date As Text Is A Direct Cell Reference, Then The Value Of The Cell Must Be Stored As A Valid Date In Text Format.
(2) DATEVALUE Function Will Return A #Value Error If Date Stored As Text Of A Reference Cell That Does Not Contain A Valid Date In Text Format.
(3) If You Provide Only Two Value Like Date And Month, Then The DATEVALUE Function Will Auto Detect The Year From Your Computer System And Will Return The Current Year Of Your System.
(4) For DATEVALUE Function, The Default Settings For The Year Are The One- And Two-Digit Years. 1 The Numbers From 0 To 29 Are Considered As The Years 2000 To 2029. 2 The Numbers From 30 To 99 Are Considered As The Years 1930 To 1999.
RESULT FORMAT: The DATEVALUE Function Will Return The Result In Serial Number As That’s How The Excel Recognize The Date. So To Get The Result Displayed In A Proper Date Format We Need To Change The Formatting Of A Cell To A Valid Date Format. Please Check The Step And Image Below.
FORMAT: Select The Cell Or Range > Right Click > Go To Number Tab. Select Date Instead Of General, As Shown Below: