VBABEGINER
Well-known Member
- Joined
- Jun 15, 2011
- Messages
- 1,232
Pls teach me how this formula works..
DateValue and TimeValue
DateValue and TimeValue
Row\Col | A | B | C | D | E |
1 | input | isnumber? | datevalue | isnumber? | |
2 | [1] | 5/8/2018 | FALSE | 43228 | TRUE |
3 | [2] | May 8 2018 | FALSE | #VALUE! | FALSE |
4 | [3] | 5/8/2018 | FALSE | 43228 | TRUE |
5 | [4] | 5/8/2018 | TRUE | #VALUE! | FALSE |
6 | [5] | 08-05-18 | FALSE | 43317 | TRUE |
7 |
Dates and times are numbers.
DATEVALUE and TIMEVALUE convert text (formatted) dates and times to true dates and times.
Row\Col A B C D E 1input isnumber? datevalue isnumber? 2[1] 5/8/2018 FALSE 43228 TRUE 3[2] May 8 2018 FALSE #VALUE! FALSE 4[3] 5/8/2018 FALSE 43228 TRUE 5[4] 5/8/2018 TRUE #VALUE! FALSE 6[5] 08-05-18 FALSE 43317 TRUE 7
<tbody>
</tbody>
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."