Need technical understanding on Formula

[VBABEGINER]

Pls teach me how this formula works..
DateValue and TimeValue

 

[MARK858]

http://lmgtfy.com/?q=datevalue+function
http://lmgtfy.com/?q=timevalue+function

 

[Aladin Akyurek]

Dates and times are numbers.
DATEVALUE and TIMEVALUE convert text (formatted) dates and times to true dates and times.

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td][/td][td]input[/td][td]isnumber?[/td][td]datevalue[/td][td]isnumber?[/td][/tr]
[tr][td]

2​

[/td][td][1][/td][td]5/8/2018[/td][td]

FALSE​

[/td][td]

43228​

[/td][td]

TRUE​

[/td][/tr]
[tr][td]

3​

[/td][td][2][/td][td]May 8 2018[/td][td]

FALSE​

[/td][td]

#VALUE!​

[/td][td]

FALSE​

[/td][/tr]
[tr][td]

4​

[/td][td][3][/td][td]5/8/2018[/td][td]

FALSE​

[/td][td]

43228​

[/td][td]

TRUE​

[/td][/tr]
[tr][td]

5​

[/td][td][4][/td][td]

5/8/2018​

[/td][td]

TRUE​

[/td][td]

#VALUE!​

[/td][td]

FALSE​

[/td][/tr]
[tr][td]

6​

[/td][td][5][/td][td]08-05-18[/td][td]

FALSE​

[/td][td]

43317​

[/td][td]

TRUE​

[/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

C2 houses:

=ISNUMBER(B2)

which is copied down.

D2 houses:

=DATEVALUE(B2)

which is copied down.

E2 houses again an isnumber test:

=ISNUMBER(D2)

copied down.

[1] contains:

'5/8/2018

[2] contains:

May 8 2018

[3] contains:

="5/8/2018"

[4] contains:

=TODAY()

[5] contains:

=TEXT(TODAY(),"dd-mm-yy")

The foregoing can be easily set up to observe the behavior of TIMEVALUE.

By the way, the following is what the 2016 version writes about DATEVALUE...
"DATEVALUE function This article describes the formula syntax and usage of the DATEVALUE function in Microsoft Excel. The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008."

 

[VBABEGINER]

Thanks for showing this.

http://lmgtfy.com/?q=datevalue+function
http://lmgtfy.com/?q=timevalue+function

 

[VBABEGINER]

Dear Sir,
Really appreciate your explanation. Just I did understand that, it only help us to give date in number format (i.e. actual format).
Thank You for this..

But why someone will use this formula..? Reason I'm asking this, I now cant find this formula much helpful while working..

Dates and times are numbers.
DATEVALUE and TIMEVALUE convert text (formatted) dates and times to true dates and times.

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]input[/TD]
[TD]isnumber?[/TD]
[TD]datevalue[/TD]
[TD]isnumber?[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD][1][/TD]
[TD]5/8/2018[/TD]
[TD]

FALSE​

[/TD]
[TD]

43228​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD][2][/TD]
[TD]May 8 2018[/TD]
[TD]

FALSE​

[/TD]
[TD]

#VALUE!​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD][3][/TD]
[TD]5/8/2018[/TD]
[TD]

FALSE​

[/TD]
[TD]

43228​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD][4][/TD]
[TD]

5/8/2018​

[/TD]
[TD]

TRUE​

[/TD]
[TD]

#VALUE!​

[/TD]
[TD]

FALSE​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][5][/TD]
[TD]08-05-18[/TD]
[TD]

FALSE​

[/TD]
[TD]

43317​

[/TD]
[TD]

TRUE​

[/TD]
[/TR]
[TR]
[TD]

7​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


C2 houses:

=ISNUMBER(B2)

which is copied down.

D2 houses:

=DATEVALUE(B2)

which is copied down.

E2 houses again an isnumber test:

=ISNUMBER(D2)

copied down.

[1] contains:

'5/8/2018

[2] contains:

May 8 2018

[3] contains:

="5/8/2018"

[4] contains:

=TODAY()

[5] contains:

=TEXT(TODAY(),"dd-mm-yy")

The foregoing can be easily set up to observe the behavior of TIMEVALUE.

By the way, the following is what the 2016 version writes about DATEVALUE...
"DATEVALUE function This article describes the formula syntax and usage of the DATEVALUE function in Microsoft Excel. The DATEVALUE function converts a date that is stored as text to a serial number that Excel recognizes as a date. For example, the formula =DATEVALUE("1/1/2008") returns 39448, the serial number of the date 1/1/2008."

 

[Aladin Akyurek]

You might get data files with text formatted dates which you need to convert. One method is to run DATEVALUE on such data.

By the way, when A2 contains a text formatted date...

=DATEVALUE(A2)

is equivalent to

=A2+0

when trying to convert.