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
Can you please help with below points :
I want to use Condition like if particular cell date is Today then result will be (this) if cell date is Tomorrow then Result will be (this) or else result will be (this).
What will be the Formula for the same...
0606
0407
0108
0509
0310
1804
2504
0205
0905
1605
2305
3005
1306
2006
2706
1107
1807
2507
0808
1508
2208
2908
1710
3110
Hi Svetlana, I have date format in Taxt (First tow Number are date and Last no are Months) as mentioned above and required date format as mentioned: 18-04-2017. Can you please help me with Formula to get this format please.
Hi Rohan,
Assuming the source data is in column A, beginning with cell A2, you can use the following formula to convert the text strings to dates:
=DATE(YEAR(TODAY()), RIGHT(A2,2), LEFT(A2,2))
The DATE function displays dates in the default short date format set in your Excel. To change the date format the way you want, please use the Format Cells dialog as explained in this tutorial:
https://www.ablebits.com/office-addins-blog/change-date-format-excel/
Or, you can nest the Date formula within the TEXT function and specify the desired format directly in the formula:
=TEXT(DATE(YEAR(TODAY()), RIGHT(A2,2), LEFT(A2,2)), "dd-mm-yyyy")
However, please keep in mind that TEXT always outputs a text string that only looks like a date.
Hi,
This Side Rakhi, Firstly you just use Short cut key (Ctrl + !) after that select date format and after that you just use short cut key (Alt + D + E + E) now fixed width, Next, Next and use Text and Finish.
I need help.
im working with conditional formating. I have this condition; when it's true the cell goes red =BA5>=DATE(2017;1;31). Everything ok. But i have already typed this date (31.1.2017) in BA5 cell, couse it's the end of the course (for my record). I would like that excel is counting days from the date i set this condition till the date that is written in cell (in my case 31.1.2017). So, if today is 26.1.2017, excel is coloring cell BA5 green. And it does that for another 4 days. 5th day its 31.1.2017 and it colors cell red.
I believe i have to include =today() function, but dont know how. I have MS Office Pro Plus 2013.
Thank you for your help
Thank you, very helpful.
Hi,
Can you please help me in creating moving vertical line with respect to week/date.
Hi,
Anyone can help.
Is there any way to calculate the nearest coupon date from today( today is included) in excel given the maturity date, coupon times per year.
Hi
I would like to calculate difference between today() and the earlier date by giving condition like if status='Closed', then it should not calculate the difference, if status='Pending', then only it should display the date difference.
regards,
Raviprasad.
Hello,
Please suggest formula for the below mention point :
date of Birth 3-Apr-59
Date of 60 year 3-Apr-19
if Day grater then 1 then date of Retirement is 30th day of 60year date otherwise it is last day of previous month of 60year date.
Suppose birthday in A1 cell
Then use this formula
=IF(DAY(A2)>1,DATE(YEAR(A2)+60,MONTH(A2)+1,1),EOMONTH(DATE(YEAR(A2)+60,MONTH(A2)-1,1),0))
ok I have a column of dates and would like to count or total by year only. Say there 1000 dates I would like to know how many time 2013 shows up, out of the 1000, and how many times 2014 does so I can make a table. the format is y/m/d in the columns. The range of years is 2004 to 2030.
Is there an Excel function that will roll out (down a column or across a row) all the dates -in order - of a given month and year?
Hello Svetlana Cheusheva,
I need a help. Actually I am a admin of a bio-metric device. There are 3 shifts are going in my office.
In time Out time
1. 6AM- 2PM
2. 2PM- 10PM
3. 10PM- 6AM
but the 3rd shift time intime 10PM is taking as
In time Out Time
day1 out time 10PM
day2 in time 6AM
How can I make it for same date 10PM as intime and 6AM as out time in excel?
Hi I am working on a "Charge, Credit, Balance worksheet"
Example: I have a tenant that in a year period has only paid 8 rent payments out of 12.I want to apply the 8 payments to the oldest balance.
Date charge Credit Balance
January 500 600 1827
Dec 500 0
Nov 500 287
Oct 500 0
Sep 357 0
Aug 357 0 0
I want to get a break down as if I apply Nov Payment to August charge, what would I be charging for Augs(I am aware that 357-287=70) so 70 will be apply to sep's charge 357+70=427-January's payment of 600. 427-600=(173)
the 173 will be apply to Otb's charge which will let a charge of 327 for Oct instead of the 500.so if I was to give a notice to pay, It would look as follow:
January 500
Dec 500
Nov 500
Oct 327
Total to collect 1827.
I do it by hand, but I would love to know the formula to save me some time.
I don't know if I made sense but, if I did can you please help me? I will greatly appreciate it. Thank you in advance.
Jess
I have a worksheet I am building for a friend & they want to be able to enter an event start & end date for the event. Once these dates are entered, I need a formula that will determine if the event occurs during either of the two stormy periods where they live.
I have been trying to just combine an if statement with an AND statement, which works just great, except I have to provide a year for the statement to work correctly. My problem is that I need the formula to work no matter what year the event occurs in. Is there a way to either get Excel to ignore the year or for me just to include a YEAR((today)) tag in my dates which delineate the stormy season?
The formula I am using is as follows:
=IF(AND(G28>=J25,G29<=K25),"Chance of Storms","Clear Weather")
where
G28 & G29 = the start & end dates for the event
J25 & K25 = the start & end date of the normally stormy season (June 17th through July 29th).
Hi,
I need a column with a date that a job position was opened, then the next column I need to have a running total number of days the position has been open that calculates daily...help :o)
Thanks!
DEAR SIR/MA'AM,
I WANT TO KNOW NUMBER OF MONTH BETWEEN TWO DATE IRRESPECTIVE OF COMPLETED MONTH.FOR EXAMPLE IF 04-APR-2016 TO 03-MAY-2016 THAN FOR WHICH FORMULA WE CAN USE TO GATE ANS 2MONTH
HOW DO I GET AMID DAYS OF START AND FINISH DATES CONSIDERING 1ST AND LAST SATURDAY AS WORKING.
Hi,
I need a formula for:
Minimum "Purchase Only" between 1/1993 and 12/1993.
Thank you.
Report Generate a serial number for events with dates
Ask a question
degree
Hello, I have a problem of generating a serial number from an event on the spreadsheet that has date of event and time, amount. how do i get the formular to compute the the corresponding slip id how do you compute such assuming it is represented in the spreadsheet like this
From: 14.11.16 00:00 To: 18.11.16 00:00
SLIP ID REG DATE ADMIN STAKE TO REGISTER STATUS IS WON? P.R.
... 14.11.16 21:43 1000.0 YES Accepted WON 1408.75
How do I calculate the number of Friday's between a current date and a future date?
Thank you,
Hello Leesha,
You can use this formula:
=INT((WEEKDAY(TODAY()-6)-TODAY()+A1)/7)
In the formula, A1 is the future date, and 6 stands for Friday. If you want to count the number of some other day of the week between two dates, you can replace 6 with other numbers between 1 (Sunday) and 7 (Saturday).
1. I want repayment schedule for individual loan where we can give logic If my repaymnet date will be a holiday like Sunday or Saturday then the repayment date will be automatically come next day.
2.I want another repayment schedule where we disburse loan to my client in two different tranche. suppose today I disbursed one tranche and another for partially utilize of loan amount.
SO please share formats.