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

7 Ways to Make Auto Date Formulas in Excel and Examples

Gambar
datetimeexcel.blogspot.com -  There are 7 formulas for creating automatic dates in Microsoft Excel, namely the TODAY, NOW, DATE, MONTH, DAY, YEAR, and DAYS formulas.  Fundamentally, to perform calculations Excel will convert date data into numeric data, so that it can be processed when creating automatic date formulas in Excel.  The following is a brief explanation in the following table. Excel formulas Uses TODAY () Today's date (dynamic) Excel formula NOW () Today's time and date Excel formula DATE Excel formulas write dates MONTH Excel formula to take the month in a cell DAY Excel formula to retrieve the date in a cell YEAR Excel formula to take the year in a cell DAYS Excel formulas calculate the difference between two dates CTRL  +  ; Shortcut to insert today's date (static) A. Excel TODAY () formula The excel formula function TODAY () is to retrieve today's data from the internal date system on the computer being used.  The TODAY () formula does not have any argu
Baca selengkapnya

EDATE function | Excel Formula Calculates Due Date

Gambar
datetimeexcel.blogspot.com -  The EDATE function  is one of the functions used to calculate the due date, so that the output of this function is the same date on the specified due month.  The due date can be set in the following months or several months in advance.  Here's a tutorial on how to use the EDATE function. A. How to Use the EDATE Function The following is the systematics of writing the EDATE function in Excel, = EDATE (  start_data  ,  months  ) start_date  is the start date of the EDATE function which can be written directly or in the form of a reference to the cell address containing the date data. Start_date   that is defined directly can use  the DATE function  or in quotes. With the DATE function  By writing in quotation marks, for example: December 31, 2018, it is written  "31-Dec-2018" months   is the number of months due to be calculated, in the form of numeric values. Positive number   for the following month, example 2 for the next 2 months. Negative
Baca selengkapnya