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 Date
EndDate: is the Second Date
Interval: This is the format or Type in which the difference you want
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
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