Chapter 4_M2

WORKING WITH FORMULAS AND FUNCTIONS CHAPTER - 4: USING FORMULAS WITH DATES AND TIMES Structure 1.0 Learning Objectives 1.1 Introduction 1.2 Date function 1.3 Time function 1.4 Summary 1.5 References 1.0 Learning Objectives After studying this unit, you will be able to:  Explain the date function in MS Excel?  Explain the Time function in MS Excel 1.1 Introduction Excel Date and Time functions can be used to extract information from, and perform operations on, Excel Dates and Times. Some of the Excel Date & Time functions were introduced to Excel 2010 or Excel 2013, so are not available in earlier versions of Excel. You can find this information in the applicability section of the function. Page 22 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS 1.2 Date Function The DATE function gives \"the sequential serial number that represents a particular date. Use it when you have to take three different values and merge them to form a date. The DATE function has the following syntax: =DATE(YEAR, MONTH, DAY). To understand this function, consider the example given below. A B CDE SR. NO. YEAR MONTH DAY RESULT 1 2019 08 19 ? 2 2019 11 5? 3 2019 10 5? Put the formula *=DATE(B1, C1, D1)in cellE1` and then apply it in subsequent rows. The result will be updated as shown below: A BC DE SR. NO. YEAR MONTH DAY RESULT 1 2019 08 19 19-08-2019 2 2019 11 5 05-11-2019 3 2019 10 5 05-10-2019 The DATEVALUE Function: The DATEVALUE function is used \"to convert a date that is available as text to a serial number that will be recognized as a date by Excel”. The DATEVALUE function has the following syntax: =DATEVALUE(date_in_the_text_format) Page 23 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS To implement the DATEVALUE function, consider the example given below: A B C Sr. No. Data Text Result “05-11-2019” =DATEVALUE(“05-11-2019”) 1 “18-08-2019” =DATEVALUE(“18-08-2019”) 2 “19-08-2019” =DATEVALUE(“19-08-2019”) 3 The result will be updated as shown below: A B C SR. NO. DATE_TEXT RESULT 1 \"05-11-2019\" 43774 2 \"18-08-2019\" 43695 3 \"19-08-2019\" 43696 1.3 Time Function Returns the decimal number for a particular time. If the cell format was General before the function was entered, the result is formatted as a date. The decimal number returned by TIME is a value ranging from 0 (zero) to 0.99988426, representing the times from 0:00:00 (12:00:00 AM) to 23:59:59 (11:59:59 P.M.). Syntax: TIME(hour, minute, second) The TIME function syntax has the following arguments: Hour: Required a number from 0 (zero) to 32767 represents the hour. Any value greater than 23 will be divided by 24 and the remainder will be treated as the hour value. For example, TIME(27,0,0) = TIME(3,0,0) = .125 or 3:00 AM. Page 24 of 63 All Rights Reserved. Vol. TLE001/03-2022

WORKING WITH FORMULAS AND FUNCTIONS Minute: Required a number from 0 to 32767 representing the minute. Any value greater than 59 will be converted to hours and minutes. For example, TIME(0,750,0) = TIME(12,30,0) = .520833 or 12:30 PM. Second: Required a number from 0 to 32767 representing the second. Any value greater than 59 will be converted to hours, minutes, and seconds. For example, TIME(0,0,2000) = TIME(0,33,22) = .023148 or 12:33:20 AM 1.4 Summary  Excel Date and Time functions can be used to extract information from, and perform operations on, Excel Dates and Times.  The DATE function gives \"the sequential serial number that represents a particular date.  Time Function states that “Returns the serial number of a particular time”. 1.5 References https://support.microsoft.com/en-us/office/time-function-9a5aff99- 8f7d-4611-845e-747d0b8d5457 https://www.tutorialspoint.com/advanced_excel_functions/advanced_e xcel_date_time_functions.htm https://www.excel-easy.com/functions/date-time-functions.html https://www.pluralsight.com/guides/working-with-time-date-functions- excel-part-1 https://support.microsoft.com/en-us/office/date-and-time-functions- reference-fd1b5961-c1ae-4677-be58-074152f97b81 Page 25 of 63 All Rights Reserved. Vol. TLE001/03-2022