Excel Formula : DATE and TIME Formula
Want to know about most of the Important Formulas in Excel? Read Excel Formulas Tutorial Page. 
Click on the below links for to know about all these Formulas and how to use them
DATE  DATEDIF  DATEVALUE  DAY  DAYS360  HOUR  MINUTE  MONTH  NOW  SECOND  TIME  TIMEVALUE  TODAY  WEEKDAY  YEAR
DATE() Formula 
What this Formula does ?
This Formula returns a Valid Date in proper format by accepting 3 Numeric values as parameters as mentioned below in the Syntax. The best part of this formula is, It does not throw any error, even if you enter the Month Number as more than 12 (Month Can be only 12) or Day Number as more than 31. In such case it calculates the next valid date based on the input parameter.
Syntax:
=DATE(YEAR,MONTH,DAY)
Where:
YEAR : Numeric Value for the Year. It accepts Two Digit of the Year format or Complete Year.
MONTH : Numeric Value for the Month.
DAY : Numeric Value for the Day.
Example:
Day  Month  Year  Date Returned by Formula  Formula Used 

10  11  95  November 10, 1995  =DATE(A1,B1,C1) 
32  12  95  January 1, 1996  =DATE(A2,B2,C2) 
30  13  95  January 30, 1996  =DATE(E6,D6,C6) 
Let’s Discuss the above example for each row:
ROW 1 : It has all valid parameters. It has all Day, Month and Year parameters as Valid one. Based on these parameters DATE() Formula returned corresponding Date.
ROW 2: It has all valid parameters except the Day. In Day parameter 32 is passed. 32 can not be a date in any of the Month of any Year. Formula will not through any error. It will check the Month. Here month is 12 i.e. December. December has 31 days. So one day is extra from the last date of December. So the Formula will automatically consider this Date as 1st January. Here year is mentioned is 95. But now the date is adjusted in January, so the Year will also be shifted to 1996. This is the reason for the ROW 2, formula Returns January 1, 1996.
ROW 3: Here Month is passed as 13. But in a Year only 12 months are possible and 12th Month is December, so 13th Month is considered as January Month of the Next Year. Therefore the formula returns as January 30, 1996
DATEDIF() Formula  TOP^ 
What this Formula does ?
Basically DATEDIF calculates difference between two dates. The most interesting part of this Function is that you can calculate the difference between two dates by a given intervals. If i say Intervals, what does it mean? Interval means, In what interval do you actually want the difference between two dates like total difference in Months, or Years or Days etc.
Syntax for the DATEDIF() Function:
=DATEDIF(StartDate, EndDate, Interval)
Where:
StartDate: is the First Date
EndDate: is the Second Date
Interval: This is the format or Type in which the difference you want
Note:
First Date should not be later than Second Date. If First Date is later than Second Date then the Formula will return an Error.
For Interval, we have few predefined Syntax, which you can use any one of them. Below is the list and Description for each of the Intervals
Interval  Meaning  Description 
d  Day  Returns Total Number of Days between Two Dates 
m  Month  Returns Total Number of Months between Two Dates 
y  year  Returns Total Number of Years between Two Dates 
yd  Days Excluding Years  Total Number of Days considering they are from the Same year. 
ym  Months Excluding Years  Total Number of Months considering they are from the Same year. 
md  Number of Days Excluding Years and Month  Total Number of Days considering they are from the Same Months and Same year. 
Note:
1. If you are giving Dates and Interval Directly in your Formula then they both must be passed in DOUBLE QUOTES (“”) otherwise you can pass the reference directly.
=DATEDIF(StartDate,EndDate,”m”)
How to Calculate Age using this Function:
Using this Function we can calculate Age of Some One just by Passing his/her Birth date. In A1 Cell the Date of Birth is Kept. Considering that you can use the following Formula.
=DATEDIF(A1,TODAY(),”y”)&” Years “&DATEDIF(A1,TODAY(),”ym”)&” Months and “&DATEDIF(A1,TODAY(),”md”)&” Days”
DATEVALUE() Formula  TOP^ 
What this Formula does ?
The DATEVALUE() Function Converts a Date, which is stored as Text in Excel, in to a Numeric Value or Serial Number, which Excel recognizes as a Date.
DATEVALUE function is helpful when you want to Filter or Sort some data based on Date Value which is stored as Text format in Excel.
Serial Number returned by this formula, can be seen as Date format by changing the Cell format as Date.
Syntax:
=DATEVALUE(date_text)
Where:
date_text : It is required. It is a Text in Excel Date format or a Cell reference which is having Text in Excel Date format. For Example 01Jan2012 or 01/01/12 etc. date_text range is from 01011900 to 31129999. If you provide any date out of this range, then this Formula will return #VALUE Error.
Remarks:
Excel stores all the dates in a sequence. It starts from 01011900 and this date is stored as 1, 02011900 as 2 and so on. Sequence Number of 01Jan2008 39448. After 39447 days from 01Jan1900, date will be 01Jan2008.
Example:
Date Text  Date Value  Formula used to get Date Value 

25DEC99  36519  =DATEVALUE(A1) 
25/12/99  36519  =DATEVALUE(A2) 
100199  36170  =DATEVALUE(A3) 
DAY() Formula  TOP^ 
What this Formula does ?
The DAY() Function returns the day of a Date or corresponding Serial Number.
Syntax:
=DAY(serial_number)
Where:
serial_number : It is required. Searial_Number is the date you want to find the Day. It can accept either a date or the corresponding date value, which you get from DATEVALUE Function.
Example:
Date  Day Returned by Formula  Formula used to get Day Value 

25DEC99  25  =DAY(A1) 
31/12/99  31  =DAY(A2) 
100199  10  =DAY(A3) 
Remarks:
Normally the result will be a number, but this can be formatted to show the actual day of the week by using Format,Cells,Number,Custom and using the code ddd or dddd.
The DAY() function can be used to calculate the name of the day for your birthday.
DAYS360() Formula  TOP^ 
What this Formula does ?
The DAYS360() Function Returns the the number of days between two days. It calculates based on 360 (30 X 12 = 360) days in a year.
Syntax:
DAYS360(start_date, end_date, [method])
Where:
start_date & end_date : These both arguement are required. These are dates between which you want the Number of Days. If start_date occurs after end_date, the DAYS360 function returns a negative number.
[method] : This is an optional Arguement. It specifies whether to use the U.S. or European method in the calculation. This is a Boolean Type arguement. If the Method is passed as TRUE, it means to use US calculation method and FALSE means to use European method in calculation.
What US and European Method of Calculation ?
US (Method – FALSE): If the starting date is the last day of a month, it becomes equal to the 30th day of the same month. If the ending date is the last day of a month and the starting date is earlier than the 30th day of a month, the ending date becomes equal to the 1st day of the next month; otherwise the ending date becomes equal to the 30th day of the same month.
European (Method – TRUE): Starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the same month.
Example:
Start Date  End Date  Days between Two Days by Formula  Formula used to Calculate 

01Jan98  05Jan98  4  =DAYS360(B1,C1,TRUE) 
01Jan98  01Feb98  30  =DAYS360(B2,C2,TRUE) 
01Jan98  31Mar98  89  =DAYS360(B3,C3,TRUE) 
HOUR() Formula  TOP^ 
What this Formula does ?
The HOUR() Function returns Hour of a given Time.
Syntax:
HOUR(serial_number)
Where:
Serial_Number: Is the time in number format. Time may be entered as Text strings within quotation marks (“5:30 AM”) or may be in Decimal format like 0.25 which represents 06:00:00 AM.
Result is always shown from 0 to 23.
Example:
Time Text  Hour  Formula used to get Hour 

21:30  21  =HOUR(B1) 
0.25  6  =HOUR(C1) 
MINUTE() Formula  TOP^ 
What this Formula does ?
The MINUTE() Function returns Minute of a given Time.
Syntax:
Minute(serial_number)
Where:
Serial_Number: Is the time in number format. Time may be entered as Text strings within quotation marks (“5:30 AM”) or may be in Decimal format like 0.25 which represents 06:00:00 AM.
Result is always shown from 0 to 59.
Example:
Time Text  Minute  Formula used to get Minute 

17072012 21:47  47  =MINUTE(B1) 
21:15:00  15  =MINUTE(C1) 
0.25  0  =MINUTE(D1) 
MONTH() Formula  TOP^ 
What this Formula does ?
The MONTH() Function returns Minute of a given Time.
Syntax:
MONTH(serial_number)
Where:
Serial_Number: is the date of the month you are trying to find. Date may be entered as Text strings like 01Jan1990 or may be the corresponding serial number of a valid date.
Example:
Original Date  Month Returned by the Formula  Formula used to get Month 

01Jan98  1  =MONTH(B1) 
01Jan98  January  =MONTH(C1) 
Remarks:
Normally the result will be a number, but this can be formatted to show the actual month by using Format,Cells,Number,Custom and using the code mmm or mmmm.
NOW() Formula  TOP^ 
What this Formula does ?
The NOW() Function shows the current date and time. The result will be updated each time the worksheet is opened and every time an entry is made anywhere on the worksheet. You can change the date and time format for the cell by using the commands in the Number group of the Home tab on the Ribbon.
Syntax:
NOW()
It does not have any arguement
Remarks:
The results of the NOW function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.
SECOND() Formula  TOP^ 
What this Formula does ?
The SECOND() Function returns Minute of a given Time.
Syntax:
SECOND(serial_number)
Where:
Serial_Number: is the time of which Second you are trying to find. Time may be entered as Text strings like 6:00 AM or may be the corresponding decimal value of a valid time.
Example:
Original Time  Second Returned by the Formula  Formula used to get Second 

4:48:18 PM  18  =SECOND(B1) 
4:48 PM  0  =SECOND(C1) 
Remarks:
Normally the result will be a number from 0 to 59.
TIME() Formula  TOP^ 
What this Formula does ?
This Formula returns a Valid Time in proper format by accepting 3 Numeric values as parameters as mentioned below in the Syntax. The best part of this formula is, It does not throw any error, even if you enter the Second Number as more than 60 (Second Can be only 60) or Minute Number as more than 60. In such case it calculates the next valid time based on the input parameter.
Syntax:
=TIME(HOUR,MINUTE,SECOND)
Where:
HOUR : Numeric Value for the Hour.
MINUTE : Numeric Value for the Minute.
SECOND : Numeric Value for the Second.
Example:
Hour  Minute  Second  Time Returned by Formula  Formula Used to get Time 

14  30  59  14:30:59  =TIME(A1,B1,C1) 
14  62  59  3:02:59 PM  =TIME(A2,B2,C2) 
14  30  63  14:31:03  =TIME(E6,D6,C6) 
Let’s Discuss the above example for each row:
ROW 1 : It has all valid parameters. It has all Second, Minute and Hour parameters as Valid one. Based on these parameters TIME() Formula returned corresponding Time.
ROW 2: It has all valid parameters except the Minute. In Minute parameter 62 is passed. 62 can not be a Minute Value. Formula will not through any error. Minute is more than 60 here. Therefore 1 hour will be added in the hour and remaining minute will be kept. So the Formula will automatically consider this Time as 15:02:59.
ROW 3: Here Second is passed as 63. But for Second more than 60 is not possible. Therefore 1 minute will be added in Minutes and and remaining second will be there in Second Part. There the time will be 14:30:59
TIMEVALUE() Formula  TOP^ 
What this Formula does ?
The TIMEVALUE() Function Converts a Time, which is stored as Text in Excel, in to a Numeric Value or Serial Number, which Excel recognizes as Time.
TIMEVALUE function is helpful when you want to Filter or Sort some data based on Time Value which is stored as Text format in Excel.
Serial Number returned by this formula, can be seen as Time format by changing the Cell format as Time.
Syntax:
=TIMEVALUE(time_text)
Where:
time_text : It is required. It is a Text in Excel Time format or a Cell reference which is having Text in Excel Time format. For Example 14:30:59.
Example:
Time Text  Time Value  Formula used to get Time Value 

14:30:59  0.604849537  =TIMEVALUE(A1) 
14:30:59  14:30:59  =TIMEVALUE(A2) 
14:30:59  2:30:59 PM  =TIMEVALUE(A3) 
TODAY() Formula  TOP^ 
What this Formula does ?
The TODAY() Function shows the current date. The result will be updated each time the worksheet is opened and every time an entry is made anywhere on the worksheet. You can change the date format for the cell by using the commands in the Number group of the Home tab on the Ribbon.
Syntax:
TODAY()
It does not have any arguement
Remarks:
The results of the TODAY() function change only when the worksheet is calculated or when a macro that contains the function is run. It is not updated continuously.
WEEKDAY() Formula  TOP^ 
What this Formula does ?
The WEEKDAY() Function shows the day of the week from a date.
Syntax:
=WEEKDAY(serial_number, [type])
Where:
serial_number : It is required. It is date for which you want to know the Day.
Type : It is an optional arguement. This is used to indicate the week day numbering system. There can be only following 3 values:
1 : will set Sunday as 1 through to Saturday as 7
2 : will set Monday as 1 through to Sunday as 7.
3 : will set Monday as 0 through to Sunday as 6.
Note : If you do not pass any value for this then the default value is considered as 1 by excel.
Example:
Date  Weekday Returned by Formula  Formula used to get Weekday 

01011998  5  =WEEKDAY(A1) 
01011998  5  =WEEKDAY(A2) 
01011998  5  =WEEKDAY(A3, 1) 
01011998  4  =WEEKDAY(A4 2) 
01011998  3  =WEEKDAY(A5 3) 
YEAR() Formula  TOP^ 
What this Formula does ?
The YEAR() Function returns Minute of a given Time.
Syntax:
YEAR(serial_number)
Where:
Serial_Number: is the date of the YEAR you are trying to find. Date may be entered as Text strings like 01Jan1990 or may be the corresponding serial number of a valid date.
Example:
Original Date  Year Returned by the Formula  Formula used to get Year 

01Jan98  1998  =YEAR(B1) 
Remarks:
Normally the result will be a number, but this can be formatted to show the actual Year by using Format,Cells,Number,Custom and using the code mmm or mmmm.
Want to Know Syntax and How to use most of the Important Formulas? Then Read Excel Formulas Tutorial Page.

My name is Vishwamitra Mishra. Friends Call me Vishwa. This blog is authored by me. I am an Excel Geek. Well, this blog talks a lot about my passion in Excels & Macros so I’ll not talk about it :) I am very much passionate about traveling & quite recently discovered that I am a good photographer too..:P

SUBSCRIBEAND GET A FREE!EBOOK FOR EXCEL VBA BEGINNERSDON'T MISS ANY NEW ARTICLE ! 
Get your FREE! EBook & Have Every New Article Delivered Straight To Your EmailBox 