Excel's DATEVALUE function and examples of its use

datetimeexcel.blogspot.com - The DATEVALUE function is a date function in Microsoft Excel that is used to convert date text data to an Excel serial number. This serial number is recognized by Microsoft Excel as a numeric value so that it can be applied to other Excel functions, such as logical and Visual Basic functions.

A. How to Use the DATEVALUE function in Excel

The following is the systematics of writing the DATEVALUE function,

= DATEVALUE (" Date_text ")

  • Date_text  is the date text data to be converted.
  • Date_text can only be written directly in quotation marks.
  • The Date_text  writing  format follows the default date format in Microsoft Excel.
    1. Format mm / dd / yy , for example 31 January 2019 is "1/31/2019"
    2. Format  dd-mm-yy , for example 31 January 2019 is "31-Jan-2019"
  • Output is a numeric value which is the Excel serial number.

B. Serial Number in Excel

The serial number generated by date functions is a standard Microsoft Excel numeric value. This serial number is used by Microsoft Excel to perform arithmetic calculations, logic, and Visual Basic programming. The serial number sequence by date starts from 1st January 1900 with 1 day intervals, i.e.

1-Jan-1900 = 1
2-Jan-1900 = 2
….
31-Jan-2019 = 43496
and so on

C. Examples of Using the DATEVALUE function in Excel

For example the text data "31-Jan-2019" will be converted using the DATEVALUE function. The following formula can be used,

= DATEVALUE (" 31-Jan-2019 ")

How to use the DATEVALUE function

You can see that the issued serial number is 43496. bandarq online

D. Use of DATEVALUE with Logic Functions

In writing Excel formulas must be defined in numeric form, so that date values ​​in Excel formulas must be converted into numeric values.

For example, a lecturer will check on students who are late in submitting assignments, if it is known that students must submit them before January 4, 2019.

Following are the data on the date of collection of assignments,

An example of using DATEVALUE

Settlement:

You can create logical formulas using the IF function with logical expressions with the DATEVALUE function.

= IF ( B2 < DATEVALUE (" 4-jan-2019 "), " Exactly ", " Late ")

Using DATEVALUE in logical functions

IMPORTANT : The use of  commas (,)  or  semicolon (;)  in the  FORMULA is  adjusted to the version of Microsoft Excel and the language configuration on your computer.

Read : how-to-use-days360-function-in-excel

Komentar

Posting Komentar

Postingan populer dari blog ini

How to Create Sequential Dates in Excel

Gambar
datetimeexcel.blogspot.com - Microsoft Excel is very helpful for speeding up the work of creating a date list.  Jobs such as making attendance lists, course lists, to daily, weekly, or monthly reports can be done by making sequential dates.  To create sequential dates in Excel, you can use the autofill feature. A. How to Create Sequential Dates in Excel For example, we will create a list of attendance for workers at a hotel event, complete with attendance dates.  This event is held from October 1, 2018 to October 7, 2018. The following is a table of freelance workers, Fill sequential date with standard mm / dd / yy format The standard format for writing dates is mm / dd / yy (according to the computer used) so that sequential dates will be created from 10/1/2018 to 10/7/2018 in the range B1: H1. Type the start date in the starting cell in the range (B1) Perform autofill from B1 to the end cell of the range (H1) For some cases, the column width is not able to display date...
Baca selengkapnya