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 01-Jan-2012 or 01/01/12 etc. date_text range is from 01-01-1900 to 31-12-9999. 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 01-01-1900 and this date is stored as 1, 02-01-1900 as 2 and so on. Sequence Number of 01-Jan-2008 39448. After 39447 days from 01-Jan-1900, date will be 01-Jan-2008.

Example:

  Date Text     Date Value     Formula used to get Date Value  
25-DEC-99 36519 =DATEVALUE(A1)
25/12/99 36519 =DATEVALUE(A2)
10-01-99 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  
25-DEC-99 25 =DAY(A1)
31/12/99 31 =DAY(A2)
10-01-99 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  
01-Jan-98 05-Jan-98 4 =DAYS360(B1,C1,TRUE)
01-Jan-98 01-Feb-98 30 =DAYS360(B2,C2,TRUE)
01-Jan-98 31-Mar-98 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  
17-07-2012 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 01-Jan-1990 or may be the corresponding serial number of a valid date.

Example:

  Original Date      Month Returned by the Formula     Formula used to get Month  
01-Jan-98 1 =MONTH(B1)
01-Jan-98 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  
01-01-1998 5 =WEEKDAY(A1)
01-01-1998 5 =WEEKDAY(A2)
01-01-1998 5 =WEEKDAY(A3, 1)
01-01-1998 4 =WEEKDAY(A4 2)
01-01-1998 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 01-Jan-1990 or may be the corresponding serial number of a valid date.

Example:

  Original Date      Year Returned by the Formula     Formula used to get Year  
01-Jan-98 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.

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

1 Comment

  1. Connie

    This is the right web site for anyone who really wants to
    understand this topic. You understand so much its almost tough to argue with you (not that I really would want to…HaHa).

    You definitely put a brand new spin on a topic that has been discussed for years.
    Excellent stuff, just excellent!

    Reply

Trackbacks/Pingbacks

  1. Calculate MOD of Large Numbers in Excel - Let's excel in Excel - […] DATEDIF WORKSHEET FUNCTION : AGE CALCULATIONEXCEL UDF: TO EXTRACT ALL NUMBERS, SPECIAL CHARACTERS AND ALPHABETS FROM A STRINGUSER DEFINED…
  2. Excel Tips : Enter Current Date Without Typing in to the Cell [Static] - Let's excel in Excel - […] Excel Formula : DATE and TIME Formula […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest