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.
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
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
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:
Syntax for the DATEDIF() Function:
=DATEDIF(StartDate, EndDate, Interval)
Where:StartDate: is the First DateEndDate: is the Second DateInterval: This is the format or Type in which the difference you want
Where:StartDate: is the First DateEndDate: is the Second DateInterval: 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
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”)
=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”
=DATEDIF(A1,TODAY(),”y”)&” Years “&DATEDIF(A1,TODAY(),”ym”)&” Months and “&DATEDIF(A1,TODAY(),”md”)&” Days”
DATEVALUE() Formula
|
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.
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.
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
|
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.
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.
The DAY() function can be used to calculate the name of the day for your birthday.
DAYS360() Formula
|
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.
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.
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
|
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.
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
|
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.
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
|
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.
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
|
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
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
|
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.
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
|
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.
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
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
|
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.
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.
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
|
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
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
|
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:
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.
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
|
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.
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.
No comments:
Post a Comment