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
Hi,
I have a timeline with moving dates, what I'm trying to do is to develop a formula that is able to determine how many days are on specific quarters of a year so for example:
This study will have a start date: Feb 14 2022, and a finish date: May 17 2022
The study cost was $3000
Considering the previous info, I want to know how many days this study has on the 1st, 2nd, 3rd and 4rd quarter of the year so that I can then split the total cost of the study per each quarter.
The quarters of the year are always de same: 1st quarter is Jan-Feb-March, the second is April-Jun-Jul and so on.
Regards,
Hello!
Your request goes beyond the advice we provide on this blog. This is a complex solution that cannot be found with a single formula. If you have a specific question about the operation of a function or formula, I will try to answer it.
Hello, i want to print to excel the dates between 1/1/2020 and 31/5/2022 but every day has to be printed 24 times. I mean ,
1/1/2020
1/1/2020
1/1/2020
1/1/2020
1/1/2020
1/1/2020
1/1/2020
1/1/2020
.
.
.
.
.
.
.
2/1/2020
2/1/2020.
.
.
.
Any help please?
Hello!
Since a date in Excel is number, you can use the SEQUENCE function to create a list of dates:
=CEILING(SEQUENCE(3624,1,1,1)/24,1)+43830
Set the date format in these cells.
Hi, I would like to know the formula on how to set up specific dates with a pattern, like for example,
08 August 2020
10 August 2020
12 August 2020
15 August 2020
17 August 2020
19 August 2020
and repeats the process up to present day. Your response is very much appreciated. Thank you!
Hello!
This data does not have a pattern, since the interval between dates is 2 and 3 days. Therefore, you cannot use a formula or auto-fill date series.
Hello Alexander, thank you for the reply. Allow me to emphasize my example. The pattern is every Monday, Wednesday and Saturday only, and those are the dates that fall within those days. I'd like to know the idea behind it to ease my task of filling the rest of the dates up to present. Hope you could provide answer(s). Thank you.
Hi!
Use the WEEKDAY function to determine the day of the week. Copy the formula from cell A2 down the column.
=IF(OR(WEEKDAY(A1,2)=1,WEEKDAY(A1,2)=6),A1+2,A1+3)
Hope this is what you need.
Hello,
Very Complex question here I believe and I'll try my best to explain the situation. In short Im trying to figure out how to return a value of a widget in a separate column, of adjacent cells, when I select a given date from a drop down list while also being able to toggle between any other date to return the value of the widget based on that date that is selected. I update the value of the widgets manually every monday and they are listed vertically in values from F5:F479. The dates are arranged horizontally from F4:XFD. So as i update the values every monday the values change in columns by 1 every time (which represents 1 week basically) but im just adding to the previous weeks list of values so that i can monitor the increase or decrease in those values over time. So column "A" contains the name of the widget, Columns "E" through the end of the entire worksheet contain the listed weekly values, Column "D" is the column I want to use to show me the value of the adjacent widget based on the date i select from the drop down list. I have defined names for certain cells and arrays to utilize index match functions as well, i just cant figure out how to combine all the different possible functions of excel to get this to work. Any help is greatly appreciated. Would love to set up a meeting even to go over this if at all possible. Thank you!!
Hello!
If my understanding is correct, the following tutorial should help: INDEX MATCH MATCH in Excel for two-dimensional lookup.
If this is not what you wanted, please describe the problem in more detail.
Hi all,
I would like to have a formula where the end result (as a date) is 1 month after the value selected, but I want it to be corrected to FRIDAY of that week, every time.
For example,
the value selected is: 25MAY22
the end result that we want: 24JUN22
===>25MAY22 + 1 month (22JUN22 - which is Wednesday) and to the Friday of that week --> 24JUN22
Is that possible to do that as a formula?
Hi!
I don't understand why you have 25 May + 1 month = 22 June. But the formula might be:
=A1+30+5-WEEKDAY(A1+30,2)
Here is the article that may be helpful to you: Add months to date in Excel. I hope it’ll be helpful.
Hey Alexander,
Thank you for you quick reply, and yes the formula worked!
By 1-month, I actually meant 4 weeks;
25MAY
1 week later: 01JUN
2 weeks later: 08JUN
4 weeks later: 22JUN
I hope this makes more sense, but regardless, thanks again.
I'm not sure if this relates specifically to any of the month formulas, but I'm trying to calculate vacation accrual each month. How can I have a specific value added to a cell automatically on the first day of each month? So, add 15 hours every 1st of the month to one cell, to continually show my vacation balance. Thanks!
Hi!
To find the day of the month from a date, use the DAY function as described in this article: Get the day of month from date in Excel (DAY function).
I hope my advice will help you solve your task.
I have 3 columns representing dates of vaccination (1st dose, 2nd dose, 3rd dose dates). I have another column with visit dates. I must compare dates of vaccination to visit date- to see how many doses they had prior to the visit date. How can i do this. some time the first dose can be blank as they are unvaccinated.
Hello!
If I got you right, the formula below will help you with your task:
=SUM((A1:C1 < D1)*(A1:C1 < > ""))
D1 - visit date
A1:C1 - dates of vaccination
Hi, is there a way to get the nth day based on a specified start month?
Day 0 is May 1, 2022
Day 1 is May 2, 2022, and so on.
If I were to input Jun 15, 2022, can it provide me what Day it is?
Thanks in advance!
Hello!
Find the date difference ( May 1 and Jun 15) as described in this guide, then subtract 1.
I am trying to figure out if there's a way to get a "live count" on a spreadsheet. For example, I deal with people with terminal illnesses. I'm trying to track their lifespan from the date of the diagnosis to see how long their lifespan is. I have it figured out once they pass away. Is there a way to have a formula that will update each day that passes from the "start date" (which is the diagnosis date)... but there is no end date yet?
Example E1 is the diagnosis date 1/20/2022, F1 is blank because there's no deceased date yet, and G1 will show "5" because today is the 5th day. Then tomorrow, it will automatically roll to "6" for the 6th day. Is this even possible to do in excel?
Hello!
Here is the article that may be helpful to you: How to calculate age in Excel.
You can use the date of diagnosis instead of the date of birth.
I hope it’ll be helpful.
Hi guys, I need help with this.
Trying to set today date into a cell with 25.12.2021
I keep getting 12/25/2021 when I use TODAY()
Any help?
Hello!
Change the date format in this cell. Use the guidelines in this article: How to change Excel date format and create custom formatting.
Hi Alexander,
Can a formula do that without going to custom formatting?
I'm trying to use a bot to set the text in the cell. This cell is also referenced by some other cells.
If there is a formula for this I will really appreciate.
Hi!
I guess you haven't read the manual from the link I gave you. There is a paragraph: How to change date format in Excel. You don't need to apply conditional formatting.
By the way, no Excel formula can change the cell format. This can be done either manually or using a VBA macro.
When I try put a date inside of if, it's doesn't work.
For example, put this formula: =IF(A2>"6/30/2039";"6/30/2039";A2)
Could you see is there something wrong?
Hello!
You can find the examples and instructions here: Using IF function with dates.
I hope my advice will help you solve your task.
I want to set up a tracker for completion of mandatory training. The training is due to be completed on induction and then every 3 years following but in a specific month. I am having trouble with rounding the year over where induction is, say October, and the training is due to be completed in January. For example, the training was completed on induction on 28/10/2021 and needs to be completed every 3 years following in January. So the next training would be due 28/01/2025 as otherwise it would be only 2 years and 3 months until the next training.
I have developed the following formula for the Next Completion Due cell (where cell D3 is the date the training is completed):
=IF(OR(D3=DATE(,1,),D3=DATE(,2,)),DATE(YEAR(D3)+4,1,DAY(D3)),DATE(YEAR(D3)+3,1,DAY(D3)))
The problem I am having is that regardless of what month I put in the D3 cell, the year is not rolling over to the next year where the training is completed in March or later (still using the January example).
So when I enter 28/1/2021 the next completion date using the above formula comes out as 28/1/2024, but when I enter 28/10/2021 the next completion date using the above formula also comes out as 28/1/2024 (and not 28/1/2025 as I am intending it to be).
Any ideas on how to get this to work for me would be greatly appreciated.
Thanks.
Hello!
Perhaps you need to add 36+3=39 months to the date. Read more in the article: How to add months to date in Excel.
I hope my advice will help you solve your task.
Created a report due date calculator to track when a report is due. (It is due no later than 16 days from admit date, but by Wednesday of that due week...) I am getting all the correct returns, with the exception of Tuesday Admits should be the same due date as the Wednesday Admits (all others are calculating as they are required per the client).
The formula I used is to generate my due date is: =IF(B15="","",B15+14-MOD(B15-4,7))
Not even sure if it is possible, but hoping! this error in due date only occurs on that every other Tuesday date, because it gets a little longer grace period than the others.
Admit Date Report Due
12/6/21 12/15/21
12/7/21 12/15/21 (this one needs to be 12/22)
12/8/21 12/22/21
12/9/21 12/22/21
12/10/21 12/22/21
12/11/21 12/22/21
12/12/21 12/22/21
12/13/21 12/22/21
12/14/21 12/22/21 (this one needs to be 12/29)
12/15/21 12/29/21
Hello!
If I understand your task correctly, the following formula should work for you:
=A2+16-WEEKDAY(A2+16,14)
This should solve your task.
This new formula worked perfectly! Thank you so much!
Works perfect! I just added in the portion to "hide" the date when it is blank:
=IF(B7="","",B7+16-WEEKDAY(B7+16,14))
Thanks again!
How to calculate commission
Source :
1)company
2) ref
If source Ref :
Ref benefits
1)01-07-2020 to 31-12-2020
(1st ref benefit-2000)
(2nd ref benefit -30000 and)
(3rd ref benefits 40000)
2) 1-1-2021 to 31 march 2021
(1st ref benefit 25000)
(2nd ref benefits 25000)
( 3rd benefit 25000)
3) 01-04-2021 To 31-03-2022
( 1 ref benefits 50000)
( 2nd ref benefit 50000)
( 3rd ref benefit 50000)
If source company benefits
Brokerage 3%
=value *brokerage commission
Please help how to calculate in one column?
Please help
Hi!
The information you provided is not enough to understand your case and give you any advice, sorry. Your question is not entirely clear, please specify.
Hello Alex, just want to ask. How to know the difference between to dates in one formula.
Ex Jan 2021 and March 2021
or March 2021 and Jan 2021
Tried using DATEDIF but having error for negative months. Thank you!
Hi!
You can use IF function:
=IF(A1>B1,DATEDIF(B1,A1,"m"),DATEDIF(A1,B1,"m"))
I hope it’ll be helpful.
Hi.. I need help. I have column A (hire date) and B(termination date)
Hire date 01/01/2000
Termination date 20/04/2021
How i want to calculate number of day started from 01/07/2020 - until termination date?
Appreciate your help
Hi!
You can find the examples and detailed instructions here: Excel DATEDIF - calculating date difference in days, weeks, months.
Please what is the formula calculating. I know the first row is the difference between the two dates
DATEDIF([Date Reported ],[Date Closed],DAY),
DATEDIF([Date],TODAY()+IF(HOUR(NOW())>14,1,0),DAY)
Hello,
I am trying to return a payroll date based on a hire date for incentive. The incentive is paid after 2 months of employment. I can use EDATE to calculate the 2 month date, but need to have the result hit the corresponding pay date. We have 24 pay periods so our pay dates are on the 15th and the final day of the month. I need the result to return one of those two days based on when the day the 2 month anniversary is hit. i.e.
Hire Date = 1/5/21
2 months = 3/5/21
Pay Date = 3/15/21
but if that person was hired on 1/18/21, then they would be paid on 3/31/21. How can I get those pay dates to calculate automatically?
Hello!
The formula below will do the trick for you:
=IF(DAY(A2)<=15,DATE(YEAR(A2), MONTH(A2) +2, DAY(A2)), EOMONTH(DATE(YEAR(A2), MONTH(A2) +2, DAY(A2)),0))
I recommend reading this guide: Subtract dates in Excel; add days, weeks, months or years to date
I hope my advice will help you solve your task.
Is there an MS Excel formula that determines the date/year of retirement after 60 years of age when the date of birth is known?
I will be very grateful!
Hello!
Add 60 years to your date of birth as described in this article. You will receive the date you want.
I hope I answered your question. If something is still unclear, please feel free to ask.
How to find smallest of set of dates.
Hi,
Please check out the following article on our blog, it’ll be sure to help you with your task: Excel SMALL function to find and highlight lowest values.
I would like to be able to conditional format cells that are within 30 days of a date shown in a cell
How would i do this?
Many thanks
Hi,
Please have a look at this article — How to conditionally format dates and time in Excel
I hope it’ll be helpful.