Date & Time

Date & Time Functions In Excel

1]   Date :  

    The DATE function returns the sequential serial number that represents a particular date.

•  Formula   :   DATE(year,month,day)
•  Example  :     

           =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and puts them into one cell as a date. The example below shows the final result in cell D2. 

2]   DATEDIF : -

    The DATE function returns the sequential serial number that represents a particular date.

•  Formula  :   DATEDIF(start_date,end_date,unit)
•  Example :            

Start_date	End_date	Formula	Description (Result)
1/1/2001	1/1/2003	=DATEDIF(Start_date,End_date,"Y")	Two complete years in the period (2)
6/1/2001	8/15/2002	=DATEDIF(Start_date,End_date,"D")	440 days between June 1, 2001, and August 15, 2002 (440)
6/1/2001	8/15/2002	=DATEDIF(Start_date,End_date,"YD")	75 days between June 1 and August 15, ignoring the years of the dates (75)

 

3]   DATEVALUE :  

    The DATEVALUE function in Excel is used to convert a date that is stored as text into a serial number that Excel can recognize as a date. For instance, if you have a date written as text like "1/1/2008," using the formula =DATEVALUE("1/1/2008") will return 39448, which is the serial number equivalent of the date 1/1/2008. Keep in mind that the results of the DATEVALUE function may vary based on your computer's system date settings.

     This function is particularly useful when you have dates stored as text in a worksheet, and you want to perform tasks such as filtering, sorting, or formatting them as dates, or using them in date calculations.

•  Syntax    :   DATEVALUE(date_text)
•  Example :     

           

Data
11
3
2011
Formula	Description	Result
=DATEVALUE("8/22/2011")	Serial number of a date entered as text.	40777
=DATEVALUE("22-MAY-2011")	Serial number of a date entered as text.	40685
=DATEVALUE("2011/02/23")	Serial number of a date entered as text.	40597
=DATEVALUE("5-JUL")	Serial number of a date entered as text, using the 1900 date system, and assuming the computer's built-in clock returns 2011 as the current year.	39634
=DATEVALUE(A2 & "/" & A3 & "/" & A4)	Serial number of a date created by combining the values in cells A2, A3, and A4.	40850

4]     DAY:  

  It Provides the day of a date, represented by a serial number, as an integer ranging from 1 to 31

•  Formula  :  DAY(serial_number)    
•  Example :            

Date
15-Apr-11
Formula	Description (Result)	Result
=DAY(A2)	Day of the date in cell A2 (15)	15

1]   Date :  

    The DATE function returns the sequential serial number that represents a particular date.

•  Syntax    :   DATE(year,month,day)
•  Example :     

           =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and puts them into one cell as a date. The example below shows the final result in cell D2. 

1]   Date :  

    The DATE function returns the sequential serial number that represents a particular date.

•  Syntax    :   DATE(year,month,day)
•  Example :     

           =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and puts them into one cell as a date. The example below shows the final result in cell D2. 

1]   Date :  

    The DATE function returns the sequential serial number that represents a particular date.

•  Syntax    :   DATE(year,month,day)
•  Example :     

           =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and puts them into one cell as a date. The example below shows the final result in cell D2. 

1]   Date :  

    The DATE function returns the sequential serial number that represents a particular date.

•  Syntax    :   DATE(year,month,day)
•  Example :     

           =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and puts them into one cell as a date. The example below shows the final result in cell D2. 

1]   Date :  

    The DATE function returns the sequential serial number that represents a particular date.

•  Syntax    :   DATE(year,month,day)
•  Example :     

           =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and puts them into one cell as a date. The example below shows the final result in cell D2. 

1]   Date :  

    The DATE function returns the sequential serial number that represents a particular date.

•  Syntax    :   DATE(year,month,day)
•  Example :     

           =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and puts them into one cell as a date. The example below shows the final result in cell D2. 

1]   Date :  

    The DATE function returns the sequential serial number that represents a particular date.

•  Syntax    :   DATE(year,month,day)
•  Example :     

           =DATE(C2,A2,B2) combines the year from cell C2, the month from cell A2, and the day from cell B2 and puts them into one cell as a date. The example below shows the final result in cell D2. 

2]