by This is the final part of our Excel Date Tutorial that offers an overview of all Excel date functions, explains their basic uses and provides lots of formula examples.
Microsoft Excel provides a ton of functions to work with dates and times. Each function performs a simple operation and by combining several functions within one formula you can solve more complex and challenging tasks.
In the previous 12 parts of our Excel dates tutorial, we have studied the main Excel date functions in detail. In this final part, we are going to summarize the gained knowledge and provide links to a variety the formula examples to help you find the function best suited for calculating your dates.
The main function to calculate dates in Excel:
Get current date and time:
Convert dates to / from text:
Retrieve dates in Excel:
Calculate date difference:
Calculate workdays:
Excel DATE function
DATE(year, month, day) returns a serial number of a date based on the year, month and day values that you specify.
When it comes to working with dates in Excel, DATE is the most essential function to understand. The point is that other Excel date functions not always can recognize dates entered in the text format. So, when performing date calculations in Excel, you'd better supply dates using the DATE function to ensure the correct results.
Here are a few Excel DATE formula examples:
=DATE(2015, 5, 20) - returns a serial number corresponding to 20-May-2015.
=DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - returns the first day of the current year and month.
=DATE(2015, 5, 20)-5 - subtracts 5 days from May 20, 2015.
At first sight, the Excel DATE function looks very simple, however, it does have a number of specificities pointed out in the Excel DATE tutorial.
Below you will find a few more examples where the Excel DATE function is part of bigger formulas:
Excel TODAY function
The TODAY() function returns today's date, exactly as its name suggests.
TODAY is arguably one of the easiest Excel functions to use because it has no arguments at all. Whenever you need to get today's date in Excel, enter the following formula is a cell:
=TODAY()
Apart from this obvious use, the Excel TODAY function can be part of more complex formulas and calculations based on today's date. For example, to add 7 days to the current date, enter the following formula in a cell:
=TODAY()+7
To add 30 weekdays to today's date excluding weekend days, use this one:
=WORKDAY(TODAY(), 30)
Note. The date returned by the TODAY function in Excel updates automatically when your worksheet is recalculated to reflect the current date.
For more formula examples demonstrating the use of the TODAY function in Excel, please check out the following tutorials:
Excel NOW function
NOW() function returns the current date and time. As well as TODAY, it does not have any arguments. If you wish to display today's date and current time in your worksheet, simply put the following formula in a cell:
=NOW()
Note. As well as TODAY, Excel NOW is a volatile function that refreshes the returned value every time the worksheet is recalculated. Please note, the cell with the NOW() formula does not auto update in real-time, only when the workbook is reopened or the worksheet is recalculated. To force the spreadsheet to recalculate, and consequently get your NOW formula to update its value, press either Shift+F9 to recalculate only the active worksheet or F9 to recalculate all open workbooks.
For more details, please see How to use NOW function in Excel.
Excel DATEVALUE function
DATEVALUE(date_text) converts a date in the text format to a serial number that represents a date.
The DATEVALUE function understands plenty of date formats as well as references to cells that contain "text dates". DATEVALUE comes in really handy to calculate, filter or sort dates stored as text and convert such "text dates" to the Date format.
A few simple DATEVALUE formula examples follow below:
=DATEVALUE("20-may-2015")
=DATEVALUE("5/20/2015")
=DATEVALUE("may 20, 2015")
And the following examples demonstrate how the DATEVALUE function can help with solving real-life tasks:
Excel TEXT function
In the pure sense, the TEXT function cannot be classified as one of Excel date functions because it can convert any numeric value, not only dates, to a text string.
With the TEXT(value, format_text) function, you can change the dates to text strings in a variety of formats, as demonstrated in the following screenshot.
Note. Though the values returned by the TEXT function may look like usual Excel dates, they are text values in nature and therefore cannot be used in other formulas and calculations.
Here are a few more TEXT formula examples that you may find helpful:
Excel DAY function
DAY(serial_number) function returns a day of the month as an integer from 1 to 31.
Serial_number is the date corresponding to the day you are trying to get. It can be a cell reference, a date entered by using the DATE function, or returned by other formulas.
Here are a few formula examples:
=DAY(A2) - returns the day of the month from a date in A2
=DAY(DATE(2015,1,1)) - returns the day of 1-Jan-2015
=DAY(TODAY()) - returns the day of today's date
Excel MONTH function
MONTH(serial_number) function in Excel returns the month of a specified date as an integer ranging from 1 (January) to 12 (December).
For example:
=MONTH(A2) - returns the month of a date in cell A2.
=MONTH(TODAY()) - returns the current month.
The MONTH function is rarely used in Excel date formulas on its own. Most often you would utilize it in conjunction with other functions as demonstrated in the following examples:
For the detail explanation of the MONTH function's syntax and plenty more formula examples, please check out the following tutorial: Using the MONTH function in Excel.
Excel YEAR function
YEAR(serial_number) returns a year corresponding to a given date, as a number from 1900 to 9999.
The Excel YEAR function is very straightforward and you will hardly run into any difficulties when using it in your date calculations:
=YEAR(A2) - returns the year of a date in cell A2.
=YEAR("20-May-2015") - returns the year of the specified date.
=YEAR(DATE(2015,5,20)) - a more reliable method to get the year of a given date.
=YEAR(TODAY()) - returns the current year.
For more information about the YEAR function, please see:
Excel EOMONTH function
EOMONTH(start_date, months) function returns the last day of the month a given number of months from the start date.
Like most of Excel date functions, EOMONTH can operate on dates input as cell references, entered by using the DATE function, or results of other formulas.
A positive value in the months argument adds the corresponding number of months to the start date, for example:
=EOMONTH(A2, 3) - returns the last day of the month, 3 months after the date in cell A2.
A negative value in the months argument subtracts the corresponding number of months from the start date:
=EOMONTH(A2, -3) - returns the last day of the month, 3 months before the date in cell A2.
A zero in the months argument forces the EOMONTH function to return the last day of the start date's month:
=EOMONTH(DATE(2015,4,15), 0) - returns the last day in April, 2015.
To get the last day of the current month, enter the TODAY function in the start_date argument and 0 in months:
=EOMONTH(TODAY(), 0)
You can find a few more EOMONTH formula examples in the following articles:
Excel WEEKDAY function
WEEKDAY(serial_number,[return_type]) function returns the day of the week corresponding to a date, as a number from 1 (Sunday) to 7 (Saturday).
- Serial_number can be a date, a reference to a cell containing a date, or a date returned by some other Excel function.
- Return_type (optional) - is a number that determines which day of the week shall be considered the first day.
You can find the complete list of available return types in the following tutorial: Day of the week function in Excel.
And here are a few WEEKEND formula examples:
=WEEKDAY(A2) - returns the day of the week corresponding to a date in cell A2; the 1st day of the week is Sunday (default).
=WEEKDAY(A2, 2) - returns the day of the week corresponding to a date in cell A2; the week begins on Monday.
=WEEKDAY(TODAY()) - returns a number corresponding to today's day of the week; the week begins on Sunday.
The WEEKDAY function can help you determine which dates in your Excel sheet are working days and which ones are weekend days, and also sort, filter or highlight workdays and weekends:
Excel DATEDIF function
DATEDIF(start_date, end_date, unit) function is specially designed to calculate the difference between two dates in days, months or years.
Which time interval to use for calculating the date difference depends on the letter you enter in the last argument:
=DATEDIF(A2, TODAY(), "d") - calculates the number of days between the date in A2 and today's date.
=DATEDIF(A2, A5, "m") - returns the number of complete months between the dates in A2 and B2.
=DATEDIF(A2, A5, "y") - returns the number of complete years between the dates in A2 and B2.
These are just the basic applications of the DATEDIF function and it is capable of much more, as demonstrated in the following examples:
Excel WEEKNUM function
WEEKNUM(serial_number, [return_type]) - returns the week number of a specific date as an integer from 1 to 53.
For example, the below formula returns 1 because the week containing January 1 is the first week in the year.
=WEEKNUM("1-Jan-2015")
The following tutorial explains all the specificities on the Excel WEEKNUM function: WEEKNUM function - calculating week number in Excel.
Alternatively you can skip directly to one of the formula examples:
Excel EDATE function
EDATE(start_date, months) function returns the serial number of the date that is the specified number of months before or after the start date.
For example:
=EDATE(A2, 5) - adds 5 months to the date in cell A2.
=EDATE(TODAY(), -5) - subtracts 5 months from today's date.
For a detailed explanation of EDATE formulas illustrated with formula examples, please see: How to use EDATE function in Excel.
Excel YEARFRAC function
YEARFRAC(start_date, end_date, [basis]) function calculates the proportion of the year between 2 dates.
This very specific function can be used to solve practical tasks such as calculating age from date of birth.
Excel WORKDAY function
WORKDAY(start_date, days, [holidays]) function returns a date N workdays before or after the start date. It automatically excludes weekend days from calculations as well as any holidays that you specify.
This function is very helpful for calculating milestones and other important events based on the standard working calendar.
For example, the following formula adds 45 weekdays to the start date in cell A2, ignoring holidays in cells B2:B8:
=WORKDAY(A2, 45, B2:B85)
For the detailed explanation of WORKDAY's syntax and more formula examples, please check out:
Excel WORKDAY.INTL function
WORKDAY.INTL(start_date, days, [weekend], [holidays]) is a more powerful variation of the WORKDAY function introduced in Excel 2010.
WORKDAY.INTL allows calculating a date N number of workdays in the future or in the past with custom weekend parameters.
For example, to get a date 20 workdays after the start date in cell A2, with Monday and Sunday counted as weekend days, you can use either of the following formulas:
=WORKDAY.INTL(A2, 20, 2, 7)
or
=WORKDAY.INTL(A2, 20, "1000001")
Of course, it might be difficult to grasp the essence from this short explanation, but more formula examples illustrated with screenshots will make things really easy:
Excel NETWORKDAYS function
NETWORKDAYS(start_date, end_date, [holidays]) function returns the number of weekdays between two dates that you specify. It automatically excludes weekend days and, optionally, the holidays.
For example, the following formula calculates the number of whole workdays between the start date in A2 and end date in B2, ignoring Saturdays and Sundays and excluding holidays in cells C2:C5:
=NETWORKDAYS(A2, B2, C2:C5)
You can find a comprehensive explanation of the NETWORKDAYS function's arguments illustrated with formula examples and screenshots in the following tutorial:
NETWORKDAYS function - calculating workdays between two dates
Excel NETWORKDAYS.INTL function
NETWORKDAYS.INTL(start_date, end_date, [weekend], [holidays]) is a more powerful modification of the NETWORKDAYS function available in Excel 2010 and later. It also returns the number of weekdays between two dates, but lets you specify which days should be counted as weekends.
Here is a basic NETWORKDAYS formula:
=NETWORKDAYS(A2, B2, 2, C2:C5)
The formula calculates the number of workdays between the date in A2 (start_date) and the date in B2 (end_date), excluding the weekend days Sunday and Monday (number 2 in the weekend parameter), and ignoring holidays in cells C2:C5.
For full details about the NETWORKDAYS.INTL function, please see:
NETWORKDAYS function - counting workdays with custom weekends
Hopefully, this 10K foot view on the Excel date functions has helped you gain the general understanding of how date formulas work in Excel. If you want to learn more, I encourage you to check out the formula examples referenced on this page. I thank you for reading and hope to see you again on our blog next week!
595 comments
Hello Svetlana,
I'm creating spread sheet where I would like to for eg. in B3 cell place a date of the project to start then in another cells will automatically change to name of the month, another will change to "day number of the month", 1, 2, 3 and so on, and another cell below will change to name of that day but single letter only (instead of Monday just M, T, W, and so on)
Will you be able to help me achieve my idea?
Best regards
Michael Nosek
Michael Nosek
(1) For display of the month number, change the format of the cell.
Go to
Home - Format Cells - Number - Custom - Type
and enter the letter m
The date value in the cell will not change, but the month number will be displayed.
(2) For display of the fist letter of the weekday, use the formula.
=CHOOSE(WEEKDAY(A2,1),"S","M","T","W","T","F","S")
Regards,
Vijaykumar Shetye, Goa, India
I am trying to create a worksheet that will provide the month of a first shipment based upon the day of the month that a product was ordered. i.e if prior to the 5th of the month the first shipment will fall within that month. After the 5th, the first shipment will be sent the following month. Additionally, I need to determine the monthly shipping schedule based on the first shipment and the product frequency purchased. The frequency options are monthly, quarterly or bi-monthly (every other month).
Can this be done with a series of date functions?
Thank you so much for your help!
Hi-
What formula could I use to determine if a 'milestone' anniversary date (5,10,15,20 years, etc.) is reached within a quarter?
So say my anniversary date is 4/6/1980 and I want to know if a milestone was reached between during the 3rd quarter current year (July 1 & Sept 30, 2015) - how can I calculate that?
Thank you!
Dear Dan,
Use the below formula.
=IF(AND(MOD(ABS(YEAR(TODAY())-YEAR(A1)),5)=0,ROUNDUP(MONTH(A1)/3,0)=ROUNDUP(MONTH(TODAY())/3,0)),"Milestone Quarter","-")
For every milestone quarter, it will display "Milestone Quarter".
Vijaykumar Shetye, Goa, India
Hi!
I'm using the =EDATE(A4,6) function. However, in my A4 cell, there is not yet a date but the function returns 182, which is 6 months from 0 (nothing in the cell). How do I get the cell that has the EDATE function to show nothing until a date is input into A4?
Thank you!
Hi Vanessa,
Just add an IF function that checks for blank cells, for example:
=IF(A4="", "", EDATE(A4,6)
I am creating a protected 60-day calendar. Cell E2 is unlocked for a date input. The calendar week starts on Monday, going through Sunday (A7-G7). The calendar grid is A8-G8 all the way down to A17-G17. I want to be able to input a date in E2, (ie:5/10/16) and for excel to know that 5/10/16 is a Tuesday, so it automatically inserts 5/10 in the cell B8. I can then formulate for the autofil of the rest of the dates all the way through the end of the 60 days.
I just cant remember how I set this up before, where excel knew which day (monday-sunday) to start the calendar by entering a date in E2...
Hi,
M having attendance data like check IN time in "A1" column Date is 16-Apr-16 and "B2" column Time is "17:55:00" and check Out date in "C1" column is "17-04-2016" and "D1" column Time is 00:53:00. Please let me know what is total duration of working hours. We require like HH:MM format.
Dear Yogesh Zagade,
Use the below formula, and format the cell in whichever format you require.
=D1-B2+C1-A1
Vijaykumar Shetye,Goa, India
Hi,
I have a monthly budget, and would like the due date to change automatically. For example, if payment is due on 5/3/2016, the following day, I want the date to change to 6/3/2016 automatically. Can you help?
Dear Lester,
Use the below formula
=MAX(A1,TODAY())
It will display the due date (5/3/2016), which is entered in cell A1, till the current date (in your case 5/3/16).
After that, it will start displaying the current date (6/3/16 onwards), every time the file calculates.
Vijaykumar Shetye, Goa, India
If you want to eliminate the negative sign, then use the function
ABS (absolute) with your formula.
Example
=ABS(TODAY()-A1) or
=ABS(A1-TODAY())
Vijaykumar Shetye,
Goa, India
I am trying to return the number of days from a set of dates (past and future) and todays date but display the resulting number of days prefixed with + (future) or - (past. Thanks.
Please help me find a formula to calculate the date it will be in 60 days (with custom holiday dates removed). I've been stumped with this. Thanks in advance!
I would like to know how to set a field to give me the next day after TODAYS date that is a certain day i.e, i want the next Thursday after today, or the next monday. etc. that auto updates when i open the spreadsheet.
FORMULA 1
=IF((7-WEEKDAY(A1,14)+1)=7,A1,A1+1+7-WEEKDAY(A1,14))
Shows the date of the current Thursday, till the end of the Thursday, and the next Thursday, after the end of Thursday.
Reference of date is in cell A1.
To change the day of week from Thursday to any other day, change the value 14 in the cell to
11 for Monday, 12 for Tuesday, ... 17 for Sunday.
FORMULA 2
=IF((7-WEEKDAY(TODAY(),14)+1)=7,TODAY(),TODAY()+1+7-WEEKDAY(TODAY(),14))
Shows the date of the current Thursday, till the end of the day, and the next Thursday, after the end of Thursday.
Automatically calculates for Today.
FORMULA 3
=IF((7-WEEKDAY(A1,1&$G$1)+1)=7,A1,A1+1+7-WEEKDAY(A1,1&$G$1))
Shows the date of the current Thursday, till the end of the day, and the next Thursday, after the end of Thursday.
Automatically calculates for Today.
Weekday to be entered in cell G1 as follows,
1 for Monday, 2 for Tuesday,... 7 for Sunday.
Kindly change the cell references in the above formulas as required.
Vijaykumar Shetye,
Goa, India
Hello,
I have a spread sheet with a Header in D1 (Issue date). I am trying to get result in Column F (Review) of "Not Due" if the date is less than 640 days from issue, "Due" if the date is between 641 to 720 days from issue, and finally "Over Due" if it is greater than 721 days from issue. I have been trying the IF function and can only seem to get 2 returns but not the third. Thanking you in advance.
David.
I think I've got it;
=IF(OR(D2=""),"",IF(D2>=TODAY()-638,"Not Due",IF(D2>=TODAY()-731,"Needs Review","Over Due")))
I have then applied Conditional formatting so that Not Due = Green, Needs Review = Yellow, and Over Due = Red
Seems to work Ok.
The formula will work correctly, but you may include a few changes in the same.
(1) The 'OR' function which you have used, is meant for checking whether 2 or more arguments are True. In your case, there is only 1 argument which 'OR' is checking. Hence it serves o purpose.
(2) Today()-638 or (any date) minus (any number) could possibly give us negative values, if the number being subtracted is sufficiently large. The possibility could be avoided by adding 638 to D2, instead of subtracting it from Today().
I have edited your formula as below.
=IF(D2="","",IF(D2+638>=TODAY(),"Not Due",IF(D2+731>=TODAY(),"Needs Review","Over Due")))
Vijaykumar Shetye,
Goa, India
I have an excel with dates in Col A and Dates in Col B with a value in Col C
8/4/2015 8/4/2015 3703
8/5/2015 8/7/2015 3705
8/6/2015 8/10/2015 3708
8/7/2015 8/11/2015 3715
8/8/2015 8/12/2015 3728
8/9/2015 8/13/2015 3731
I would like to move the dates in Col B along with the value (which represents how many students made enquiries for our programs on that day) in Col C to line up with the date in Col A. Is there a formula for such an endeavor
Assuming that your data is in cells A1 to C6,
Paste the following formula in cell A7
=TEXT(B1,"dd/mm/yyyy")&" "&C1
The displayed result will be
08/04/2015 3703
Is this what you want to do?
Vijaykumar Shetye,
Goa, India
I have once Excel file in which i have 52 columns considering as weeks of the year. Then if the cell is equal to current date then i have to display the value from other sheet.Please help
Dear Syed Raheemuddin,
I have not understood your question. Please explain it in detail.
If a cell is equal to current date, then how will you display the value form another sheet in the cell?
When posting a question, please be very clear and concise.
Vijaykumar Shetye, Goa, India
Currently, I'm using the 'weekday' formula in which the dates on the left give me the dates on the right.
=B2-WEEKDAY(B2-6)
Friday, March 18, 2016 11-Mar
Saturday, March 19, 2016 18-Mar
Sunday, March 20, 2016 18-Mar
Monday, March 21, 2016 18-Mar
Tuesday, March 22, 2016 18-Mar
Wednesday, March 23, 2016 18-Mar
Thursday, March 24, 2016 18-Mar
Friday, March 25, 2016 18-Mar
Saturday, March 26, 2016 25-Mar
Sunday, March 27, 2016 25-Mar
I'm trying to tweak the equation so that the pattern will look like the following: Basically, I'm trying to shift the dates on the right up by two (i.e. 3/19 will now reflect 3/11, 3/20 will now reflect 3/11, 3/26 will reflect 3/18, 3/27 will now reflect 3/18)
Friday, March 18, 2016 11-Mar
Saturday, March 19, 2016 11-Mar
Sunday, March 20, 2016 11-Mar
Monday, March 21, 2016 18-Mar
Tuesday, March 22, 2016 18-Mar
Wednesday, March 23, 2016 18-Mar
Thursday, March 24, 2016 18-Mar
Friday, March 25, 2016 18-Mar
Saturday, March 26, 2016 18-Mar
Sunday, March 27, 2016 18-Mar
Any assistance on how to formulate that would be greatly appreciated.
hi everyone :)
i have date format in see (1)
need convert to this format in excel see (2)
1) 16-03-2016 5:46 PM
2) 3/12/2016 1:13:00 AM
Go to Home - Format Cells - Number Type, and
Change the format of the cell to
d/mm/yyyy h:mm:ss AM/PM
Vijaykumar Shetye,
Goa, India
Hi,
I want to identify with the ageing of the current time by considering it as non communicating 6 hours
Ex: 18-03-2016 12:28 (>6 hours forumla should show as "communicating")
18-03-2016 12:28 (6< hours forumla should show as "noncommunicating")
Thanks
To make it more simple.
Here is the date and time
18-03-2016 12:37
(=IF(L2>=TIME(20,59,59)+ TIME(21,0,0),"communicating","noncommunicating")
Hi,
I am interested in a date formula that will allow me to enter data into other fields and then having that date "stamped" when the data was entered
example: in cell A1 have a name typed and then in A2 have today's date appear (and not change).
Let me know
Thank you
Hello, Scott,
Уou need a VBA script for this. Sorry, we cannot help you with it.
20180502
20170101
20180301
20190802
20170901
20180601
20170201
20160601
how to convert in to date yyyy-mm-dd 16'oct convert in 10-2016
please help me,
i have online software date format (14/03/2016 3:07 PM) i need to convert this format (Mar/14/2016)
please help
Dear Munawwer Khan,
Select the cell and go to Home - Number - Custom - Type,
and enter the below format
mmm/dd/yyyy.
The value of the cell will not change, but it will be displayed in the type of format you require.
Vijaykumar Shetye, Goa, India
I need a formula which when entered in a cell of a Colum will automatically add date and time base on the present date and time on the rows down the Colum as I populate other cells in an excel sheet.
Hello,
Looks like you need VBA for this. Sorry, we cannot help you with it.