Below are few of the important functions related to date, which are available in excel. These functions bring great help in our day to day calculation activities.
1. YEAR
Formula:
=YEAR(Date)
The YEAR formula is useful, when we need to extract only the year from the date.
Example:
We have around 10000 rows with dates of different years with sales data. While we need to create a chart that shows the yearly trend of total sales. For this we need to first find out the Year using the formula:
A1 = 26-Nov-14
The formula =YEAR(A1) will give us the output as 2014. We can copy paste this formula in all cells to get the Year output. Once we have the Year, we can then create a pivot table and create a chart to show the yearly trend for Sales.
2. MONTH
Formula:
=MONTH(Date)
The MONTH formula is useful, when we need to extract only the month from the date.
Example:
The same example in the YEAR formula above, can be used here to show the monthly trend for sales.
The formula =MONTH(A1) will give us the output as 11, as the month in the date is November. Once we have the month we can create a pvot table and create a chart to show the monthly trend for sales.
3. DAY
Formula:
=DAY(Date)
The DAY formula is used to extract only the day for the month from the selected date.
Example:
We will use the same date in the above examples in cell A1. The formula =DAY(A1) will give us the output as 26. As it is the 26th Day of November. This can be useful when you need to do some analysis based on only days.
4. DATE
Formula:
=DATE(year, month, day)
The date formula can be used to seperate the date time format date with simple date. Or in case we need to convert the date into serial number.
Example:
A1 = 11/26/2014 12:39:00 PM
We need to convert the date time in cell A1 to simple date format, for that we can use the DATE formula with the combination of above three formulas i.e., YEAR, MONTH and DAY.
The formula =DATE(YEAR(A1),MONTH(A1),DAY(A1)) will give the output as 11/26/2014.
This formula can also be used with combination of other formulas to convert the text into Date formats.
eample: A2 = 20141126 and we need to convert this in simple date format. We can use the DATE formula with the combinations of the LEFT, MID and RIGHT function:
=DATE(LEFT(A2,4),MID(A2,5,2), RIGHT(A2,2))
This will give us the output as 11/26/2014.
5. DAYS360
Formula:
=DAYS360(Start Date, End Date, Method)
The DAYS360 feature returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. We can use this function to help compute payments if your accounting system is based on twelve 30-day months.
The method in the above formula is optional, you can use it only if you need some specific methods mentioned below for computing.
FALSE or omitted:
U.S. (NASD) method. 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.
TRUE:
European method. Starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the same month.
Note:
Excel stores the dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2014 is serial number 41,640 because it is 41,639 days after January 1, 1900.
6. EDATE
Formula:
=EDATE(Start Date, Months)
This is an very useful date function which helps us to get the future or past dates after or before specific date.
The EDATE formula can be used to calculate the warranty expiring dates from the date of purchase, maturity dates or due dates that fall on the same day of the month as the date of issue.
Example:
Say we need to find out the date after 18 months and the date in cell A1 = 31-Dec-14, then the formula =EDATE(A1,18) will give us the output as 30-Jun-16. Another example of finding past dates for 19 days, the the formula =EDATE(A1,-19) will provide us the output as 31-May-13.
7. EOMONTH
Formula:
=EOMONTH(Start Date, Months)
The EOMONTH formula is somewhere similar to the EDATE function, the only difference here is that this formula returns the last date of the output month. This formula can be used to caluclate the due dates or maturity dates that always fall on the last day of the month.
Example:
Say we need to find out the date after 12 months and the date in cell A1 = 26-Nov-14, then the formula =EOMONTH(A1,12) will give us the output date as 30-Nov-15. Similarly to find out the past 12 month date, we can use the formula =EOMONTH(A1,-12), which will give us the output as 30-Nov-13.
8. NETWORKDAYS
Formula:
=NETWORKDAYS(Start Date, End Date, # of holidays)
The NETWORKDAYS formula give us the number of only working days between Start Date and End Date. Working days exclude weekends and any dates identified in holidays. We can use the NETWORKDAYS formula to calculate the employee benefits that accrue based on the number of days worked during a specific term.
The # of holidays in the above formula, is an optional range of one or more dates to exclude from the working calendar, such as the government holidays. The list can be a range of cells that contains the dates.
Example:
A1 = 1-Nov-14 and A2 = 26-Nov-14
Then the formula =NETWORKDAYS(A1,B1) will give us the output as 18 days. This formula excludes the weekends from the days difference count. In one more example where we have holidays, say we have the dates in cell C1 = 4-Nov-14 and C2 = 5-Nov-14, then the formula =NETWORKDAYS(A1,B1,C1:C2) will give us the output as 16, now in this formula the weekends + holidays listed in C1 and C2 are excluded in the days.
9. NOW
Formula:
=NOW()
The NOW formula give us the current date and time. If the cell format was General before the function was entered, Excel changes the cell format to the same date and time format that is specified in the regional date and time settings in Control Panel.
The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.
Note:
If the NOW function does not update cell values when you expect it to, you might need to change settings that control when the workbook or worksheet recalculates. In the Formulas category under Calculation options, make sure that Automatic is selected to calculate the formulas automatically.
Example:
The formula =NOW() will give us the output as 11/26/2014 14:36, this is the current date and time when the function was entered in excel. This date and time will keep on changing, whenever the sheet reclculates or whenever the sheet is re-opened. This function will always return the current date and time in the system.
10. TODAY
Formula: =TODAY()
The formula =TODAY() will return the current date. If the cell format was General before the function was entered, Excel changes the cell format to Date. The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals.
Example:
You can calulate the age of the person. Say if your birth year is 1984 which is the value in cell A1 and =today() in cell A2, then the below formula can be use to find that person's age as of this year's birthday:
=YEAR(A2)-A1 this formula will give us the output as 30.
in above formula we have used the YEAR formula to get the YEAR from TODAY() and then subtracted it to the birth year i.e., 2014-1984 = 30 yrs.
11. WEEKNUM
Formula:
=WEEKNUM(Date,Day)
The WEEKNUM formula provides us with the week number of a specific date.
For example, the week containing January 1 is the first week of the year, and is numbered week 1.
There are two systems used for this function:
System 1: The week containing January 1 is the first week of the year, and is numbered week 1.
System 2: The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1.
Example:
A1 = 1-Nov-14
The formula =WEEKNUM(A1) will give us the output as week 45. Say we use the formula,
=WEEKNUM(A1,2), this formula will give us the output as 44. Here the 2 represents the week starting on Monday or if we use 1 then week starting on Sunday. In case of 1 in the same formula the output will be 45. If nothing is entereted in the Day part in above formulam, then the WEEKNUM will by default consider 1(Sunday) to calculate the week number.
The formula
12. WEEKDAY
Formula:
=WEEKDAY(Date,Type)
The WEEKDAY formula gives us the the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
Example:
A1 = 1-Nov-14
The formula =WEEKDAY(A1) will give us the output as 7 which is Saturday i.e., 7th day of the week. If you further use the formula =WEEKDAY(A1,2) will give you the output as 6. Here the WEEKDAY is starting from 2 (Monday) hence, from monday Staturday is the 6th Day, hence the formula returns 6th Day of the week.
Note:
By default the WEEKDAY formula considers the weekstart from day 1(Sunday). To change this further you can use 1,2 3....
1. YEAR
Formula:
=YEAR(Date)
The YEAR formula is useful, when we need to extract only the year from the date.
Example:
We have around 10000 rows with dates of different years with sales data. While we need to create a chart that shows the yearly trend of total sales. For this we need to first find out the Year using the formula:
A1 = 26-Nov-14
The formula =YEAR(A1) will give us the output as 2014. We can copy paste this formula in all cells to get the Year output. Once we have the Year, we can then create a pivot table and create a chart to show the yearly trend for Sales.
2. MONTH
Formula:
=MONTH(Date)
The MONTH formula is useful, when we need to extract only the month from the date.
Example:
The same example in the YEAR formula above, can be used here to show the monthly trend for sales.
The formula =MONTH(A1) will give us the output as 11, as the month in the date is November. Once we have the month we can create a pvot table and create a chart to show the monthly trend for sales.
3. DAY
Formula:
=DAY(Date)
The DAY formula is used to extract only the day for the month from the selected date.
Example:
We will use the same date in the above examples in cell A1. The formula =DAY(A1) will give us the output as 26. As it is the 26th Day of November. This can be useful when you need to do some analysis based on only days.
4. DATE
Formula:
=DATE(year, month, day)
The date formula can be used to seperate the date time format date with simple date. Or in case we need to convert the date into serial number.
Example:
A1 = 11/26/2014 12:39:00 PM
We need to convert the date time in cell A1 to simple date format, for that we can use the DATE formula with the combination of above three formulas i.e., YEAR, MONTH and DAY.
The formula =DATE(YEAR(A1),MONTH(A1),DAY(A1)) will give the output as 11/26/2014.
This formula can also be used with combination of other formulas to convert the text into Date formats.
eample: A2 = 20141126 and we need to convert this in simple date format. We can use the DATE formula with the combinations of the LEFT, MID and RIGHT function:
=DATE(LEFT(A2,4),MID(A2,5,2), RIGHT(A2,2))
This will give us the output as 11/26/2014.
5. DAYS360
Formula:
=DAYS360(Start Date, End Date, Method)
The DAYS360 feature returns the number of days between two dates based on a 360-day year (twelve 30-day months), which is used in some accounting calculations. We can use this function to help compute payments if your accounting system is based on twelve 30-day months.
The method in the above formula is optional, you can use it only if you need some specific methods mentioned below for computing.
FALSE or omitted:
U.S. (NASD) method. 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.
TRUE:
European method. Starting dates and ending dates that occur on the 31st day of a month become equal to the 30th day of the same month.
Note:
Excel stores the dates as sequential serial numbers so that they can be used in calculations. By default, January 1, 1900 is serial number 1, and January 1, 2014 is serial number 41,640 because it is 41,639 days after January 1, 1900.
6. EDATE
Formula:
=EDATE(Start Date, Months)
This is an very useful date function which helps us to get the future or past dates after or before specific date.
The EDATE formula can be used to calculate the warranty expiring dates from the date of purchase, maturity dates or due dates that fall on the same day of the month as the date of issue.
Example:
Say we need to find out the date after 18 months and the date in cell A1 = 31-Dec-14, then the formula =EDATE(A1,18) will give us the output as 30-Jun-16. Another example of finding past dates for 19 days, the the formula =EDATE(A1,-19) will provide us the output as 31-May-13.
7. EOMONTH
Formula:
=EOMONTH(Start Date, Months)
The EOMONTH formula is somewhere similar to the EDATE function, the only difference here is that this formula returns the last date of the output month. This formula can be used to caluclate the due dates or maturity dates that always fall on the last day of the month.
Example:
Say we need to find out the date after 12 months and the date in cell A1 = 26-Nov-14, then the formula =EOMONTH(A1,12) will give us the output date as 30-Nov-15. Similarly to find out the past 12 month date, we can use the formula =EOMONTH(A1,-12), which will give us the output as 30-Nov-13.
8. NETWORKDAYS
Formula:
=NETWORKDAYS(Start Date, End Date, # of holidays)
The NETWORKDAYS formula give us the number of only working days between Start Date and End Date. Working days exclude weekends and any dates identified in holidays. We can use the NETWORKDAYS formula to calculate the employee benefits that accrue based on the number of days worked during a specific term.
The # of holidays in the above formula, is an optional range of one or more dates to exclude from the working calendar, such as the government holidays. The list can be a range of cells that contains the dates.
Example:
A1 = 1-Nov-14 and A2 = 26-Nov-14
Then the formula =NETWORKDAYS(A1,B1) will give us the output as 18 days. This formula excludes the weekends from the days difference count. In one more example where we have holidays, say we have the dates in cell C1 = 4-Nov-14 and C2 = 5-Nov-14, then the formula =NETWORKDAYS(A1,B1,C1:C2) will give us the output as 16, now in this formula the weekends + holidays listed in C1 and C2 are excluded in the days.
9. NOW
Formula:
=NOW()
The NOW formula give us the current date and time. If the cell format was General before the function was entered, Excel changes the cell format to the same date and time format that is specified in the regional date and time settings in Control Panel.
The NOW function is useful when you need to display the current date and time on a worksheet or calculate a value based on the current date and time, and have that value updated each time you open the worksheet.
Note:
If the NOW function does not update cell values when you expect it to, you might need to change settings that control when the workbook or worksheet recalculates. In the Formulas category under Calculation options, make sure that Automatic is selected to calculate the formulas automatically.
Example:
The formula =NOW() will give us the output as 11/26/2014 14:36, this is the current date and time when the function was entered in excel. This date and time will keep on changing, whenever the sheet reclculates or whenever the sheet is re-opened. This function will always return the current date and time in the system.
10. TODAY
Formula: =TODAY()
The formula =TODAY() will return the current date. If the cell format was General before the function was entered, Excel changes the cell format to Date. The TODAY function is useful when you need to have the current date displayed on a worksheet, regardless of when you open the workbook. It is also useful for calculating intervals.
Example:
You can calulate the age of the person. Say if your birth year is 1984 which is the value in cell A1 and =today() in cell A2, then the below formula can be use to find that person's age as of this year's birthday:
=YEAR(A2)-A1 this formula will give us the output as 30.
in above formula we have used the YEAR formula to get the YEAR from TODAY() and then subtracted it to the birth year i.e., 2014-1984 = 30 yrs.
11. WEEKNUM
Formula:
=WEEKNUM(Date,Day)
The WEEKNUM formula provides us with the week number of a specific date.
For example, the week containing January 1 is the first week of the year, and is numbered week 1.
There are two systems used for this function:
System 1: The week containing January 1 is the first week of the year, and is numbered week 1.
System 2: The week containing the first Thursday of the year is the first week of the year, and is numbered as week 1.
Example:
A1 = 1-Nov-14
The formula =WEEKNUM(A1) will give us the output as week 45. Say we use the formula,
=WEEKNUM(A1,2), this formula will give us the output as 44. Here the 2 represents the week starting on Monday or if we use 1 then week starting on Sunday. In case of 1 in the same formula the output will be 45. If nothing is entereted in the Day part in above formulam, then the WEEKNUM will by default consider 1(Sunday) to calculate the week number.
The formula
12. WEEKDAY
Formula:
=WEEKDAY(Date,Type)
The WEEKDAY formula gives us the the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default.
Example:
A1 = 1-Nov-14
The formula =WEEKDAY(A1) will give us the output as 7 which is Saturday i.e., 7th day of the week. If you further use the formula =WEEKDAY(A1,2) will give you the output as 6. Here the WEEKDAY is starting from 2 (Monday) hence, from monday Staturday is the 6th Day, hence the formula returns 6th Day of the week.
Note:
By default the WEEKDAY formula considers the weekstart from day 1(Sunday). To change this further you can use 1,2 3....
No comments:
Post a Comment