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
What formula should i use so that whenever i enter date and time it can get converted to my desired time zone for example :- IST to CST
Hi! You can select the location you want and set a custom date format as described in these instructions: How to change date format in Excel and create custom formatting.
I need to generate a date for the 4th Wednesday of each month for the next 12 months. Is there a formula that would make that happen beginning in April of 2024?
Hi! Create a date sequence using the SEQUENCE function as described in this manual: How to create a sequence of dates in Excel and auto fill date series.
To obtain only the fourth Wednesday of each month from this sequence, use the FILTER function. To find the day of the month, use the DAY function. It must be above 20. The number of the day of the week using the WEEKDAY function must be equal to 3.
=FILTER(SEQUENCE(365,1,45383,1), (DAY(SEQUENCE(365,1,45383,1))>20) * (WEEKDAY(SEQUENCE(365,1,45383,1),2)=3))
Remember to set the cells to the desired date format.
I hope that the advice I have given you will help you solve your problem.
I need to convert full date (source date) to MO/YR that is a month after the source date. For example: 12/31/2023 needs a formula to get a result of 1/2024.
Hi! Use the EOMONTH function to get the date of the next month. To get the format of the value, use the TEXT function. You can also set a custom date format in the cell. Try this formula:
=TEXT(EOMONTH(A1,1),"m/yyyy")
How does it convert to expiration dates? I am not seeing examples. I have current dates in cells but I need to know when it is 60 days to expiring.
Hi! If I understand your task correctly, this article may be helpful: Calculate number of days between two dates in Excel. If you want to highlight dates by condition, use this guide: Excel conditional formatting for dates & time: formulas and rules.
re: year function has hardly any problems.
NOT TRUE! when I do as you say, the date is converted from something recent into some date in 1905. Doesn't work at all
Hi! Please specify what you were trying to find, what formula you used, and what issue or error occurred. Include an example of the source data and the result you want to get.
Hi - i have date and time in a cell. I wish to convert this to something like "Sun, Feb 4, 2024". What is the correct formula to do this? I am using =DateTime X - WEEKDAY(DateTime X,1)+1. This converts to any date/time to a Sunday of the week. When I try to sort in the Pivot table, these dates are sorted in Alphabetical order and not in the ascending/descending date order. Any ideas on what am I doing wrong? Any better formulas to convert any given datetime to the followng format "Sun, MMM DD, YYYY". Thanks.
Hello! Try to follow the recommendations from this article: How to change Excel date format and create custom formatting. Try to use this custom date format:
ddd, mmm d, yyyy
I'm wanting to reference a date in cell A1, in a formula, example CountIF=(H2:H50">=A1"). This way when I change the date in A1 the formulas will still work.
Hi! Look for the example formulas here: Using Excel COUNTIF function with dates.
Hello,
I have a question, say if have a tracker that is tracking a running date from a column I have received an approval and I have another column where we have finalized the task. I want the running date to stop once there is an input in the date finalized column, but this wont affect other rows that has not finalized their tasks yet. 3 columns Date Form Approved, Age (Running days) and Date Task Finalized. This is for a Recruitment Tracker I am working on.
Thank you!
Hi! If I understand your task correctly, the following tutorial should help: How to insert today date & current time as unchangeable time stamp. The second option is to use VBA to write the current date and time to the cell.
Hi,
I am putting a date in A1 and in A2 I would like to add 7 days. this is easy enough, however I would like the cell to remain empty if the A1 date is not filled in, and the formula to stay and fill in automatically once the date is put in. So far I have not been successful with any if formulas and if I just use=A1+7 when there is no date it shows 07/01/00 instead of remaining empty. Can you please help? I have been trying to search for this for hours without success.
Thank you
Hi! The answer to your question can be found in this guide: Excel formula: if cell is blank then.
For example:
=IF(ISBLANK(A1), "", A1+7)
I am trying to generate an aged creditors report in excel that calculates how many days overdue an invoice is based on a fix dated.
DATEDIF function =DATEDIF("invoice due date", "fixed date", "d") works for all the invoices that are overdue and a number is returned however for all invoices that are not due yet i get the error #NUM!
I know i can use the TODAY() function and it provides the necessary results, but i dont know how to lock that date in so that the returned figure doesn't change each day.
Can you please advise
Hi! I’m not sure if I got you right, since the description you provided is not entirely clear. If you want to remove the error message, try the IFERROR function. For example:
=IFERROR(DATEDIF("invoice due date", "fixed date", "d"), DATEDIF("invoice due date", TODAY(), "d"))
If it's not what you wanted, explain more accurately what result you wanted.
Sorry if i wasn't clear.
I either need help clearing the error message so that the formula works to give a negative number for the invoices that are not due
Or
I need to know how to use the TODAY function but have that has a fixed date as opposed to date that changes each day.
Hi! If you want to get a negative number of days, change the IFERROR function. For example:
=IFERROR(DATEDIF("invoice due date", "fixed date", "d"), -DATEDIF("fixed date","invoice due date", "d"))
On the second question, you might find it helpful: How to insert today date & current time as unchangeable time stamp. The second option is to use VBA to write the current date to the cell.
Since the new year 2024 started, the following formula bring wrong number of "days". The year and month are correct.
=DATEDIF(A2,TODAY(),"y") & " : " & DATEDIF(A2,TODAY(),"ym") & " : " & DATEDIF(A2,TODAY(),"MD")
For eg., if we input the source date as 02-12-2014, the result is correct with 9:1:0. If we input 03-12-2014, then the result is wrong with 9:0:143.
Please suggest correct forumula.
I got the correct result from your data 03-12-2014. Check what you enter in the cell. I think it's New Year's fault.
Sorry, I forgot to mention one thing that please check for one day "later date" input. For eg., if you are checking on 6-Jan, then put 7-Jan as input. I have posted my query on 2-Jan, and you have checked on 3-Jan. So, naturally, the answer will be correct with "0".
Also, I made one more mistake in my first query - the year "2014". It should be 2023. To be clear, please read as follows.
The following formula brings wrong number of "days" in the scenario given below the formula. The year and month are correct.
=DATEDIF(A2,TODAY(),"y") & " : " & DATEDIF(A2,TODAY(),"ym") & " : " & DATEDIF(A2,TODAY(),"MD")
Scenario: If A2 date is 05-Dec-2023 when i check on 05-Jan-2024, the result is correct with 0:1:0. If I change A2 to 06-Dec-2023 (ie., one "day" more than the "day" i check), then the result is wrong with 0:0:143.
So, when you are checking, please change the A2 date at least one day more from the current day you are checking on the formula. Eg., if you are checking on 06-Jan-2024, then the date in A2 should be 07-Dec-2023. The result comes 0:0:143. Please help.
Hi! Date in A2 = 07-Dec-2023. The result 0:0:29. Date in A2 = 06-Dec-2023. The result 0:0:30.
I create a file daily and the header has the next business day's date (ie. Today is Thursday so the file I would be creating would be for Friday and if it's Friday, I'd need to make it for Monday.) Is there a formula I can use in my header that would automatically insert the next business day's date in the day, month, date, and year text format (Friday, November 17, 2023)? Thank you in advance.
Hi! To determine the next working day, use the WORKDAY function. To write the date as text, use the TEXT function and the date format you want. Try this formula:
=TEXT(WORKDAY(TODAY(),1),"dddd, mmmm dd, yyyy")
I'm struggling on setting a limit to getting data. You see we have graveyard shifters so if someone is scheduled at 9 PM, his shift ends at 6 AM the next day. What I'm trying to accomplish is - is there any way I can get all data only that's within that window?
Hi! Based on your description, it is hard to completely understand your task. However, I’ll try to guess and offer you FILTER formula. To create a condition when the data is between 9PM and 6AM the next day, try using these guidelines: Excel IF between two dates.
I hope it’ll be helpful. If this does not help, explain the problem in detail.
Something tells me that there is a simple formula for this, but I am unable to find anything in searching. I am looking to set up a validation to check the value of the adjacent column for a value and then offer a limited range based on that value. In this case, I have a column for Decades, the next column is for the Years. I want to be able to limit the selection of years based on the decade selected. For ex. if I choose "1980" for the decade, I want the adjacent cell in the next column to provide a validation list of the years: "1980, 1981, 1982, ... 1989" only. If the next row I select "2000", then I only want the years 2000 - 2009 listed in the validation.
Is there a formula can I use to provide that in a validation?
Thanks.
Hi! To get a list of valid years based on the value in cell A2, use the formula:
=A2+ROW(A1:A10)-1
You can use this formula to data validation in cell B2.
You can also create a drop-down list using this formula. Write this formula in cell D1, for example, and create a named range from cells D1:D10 with the name Decades. Then create a drop-down list with the named range Decades as the data source:
=Decades
Read more: Insert drop down list from a named range.
THANK YOU! That worked great!
To all Excel Experts,
I am calculating the NEXT date of service based on the Maximum or next engine hour;
Last Service Eng. Hr During Last Svc "Current Engine Hr (02-Nov) Engine Hr For NEXT Service "Date of NEXT Service
2-Nov-23 16148 16148 16398 ???
29-Oct-23 11279 11292 11529 ???
Kindly show me a formula how to have the dates when Engine hour for Next Service change.
Hi! If I understand your task correctly, convert the number of hours to time as described in this manual: How to convert numbers to time format in Excel. Then add that time to the date of service. Read more: How to add and subtract dates, days, weeks, months and years in Excel. For example:
=A2+B2/24
To all the excel experts, I hope someone could assist or show me how to create formulas with multiple conditions.
A B C D E F
No. Date Report No. Reason Report returned? Y, N, NA FOLLOW UP NEEDED? DUE, OVERDUE, NOT DUE, NO ACTION NEEDED
1 01/10/2023 23S16387 Second Opinion Y EXCEL FORMULA?
2 05/10/2023 23S16767 Genomics N EXCEL FORMULA?
3 09/10/2023 23S15993 Trial NA EXCEL FORMULA?
4 25/10/2023 23S15300 MDT NA EXCEL FORMULA?
Need to follow up second opinion and genomics only
Y Means report returned and no need for follow up
N report not returned and need follow up
NA NOT APPLICABLE means no report needed
EXCEL FORMULA
IF Report returned=Y should indicate NO ACTION NEEDED
IF Report returned=N and IF DATE is = or more than 14 days should indicate DUE
IF Report returned=N and IF DATE is = or more than 30 days should indicate OVERDUE
IF Report returned=N and IF DATE is less than 14 days should indicate NOT DUE
IF Report returned=NA NO ACTION NEEDED
I need help with if multiple formulas for follow up reports, to be able to check daily updates.
I tried IF and and function but its also error its multiple conditions
To all the excel experts, I hope someone could assist or show me how to create formulas with 2 or 3 conditions.
I need help with if multiple formulas for follow up reports, to be able to check daily updates
A B C D E F
No. Date Report No. Reason Report returned? Y, N, NA FOLLOW UP NEEDED? DUE, OVERDUE, NOT DUE, NO ACTION NEEDED
1 01/10/2023 23S16387 Second Opinion Y EXCEL FORMULA?
2 05/10/2023 23S16767 Genomics N EXCEL FORMULA?
3 09/10/2023 23S15993 Trial NA EXCEL FORMULA?
4 25/10/2023 23S15300 MDT NA EXCEL FORMULA?
Need to follow up second opinion and genomics only
Y Means report returned and no need for follow up
N report not returned and need follow up
NA NOT APPLICABLE means no report needed
EXCEL FORMULA
IF E=Y NO ACTION NEEDED
IF E=N and IF B is = or more than 14 days DUE
IF E=N and IF B is = or more than 30 days OVERDUE
IF E=N and IF B is less than 14 days NOT DUE
IF E=NA NO ACTION NEEDED
Hi! To write a formula with multiple conditions, use the IFS function. To fulfill two conditions at once, use the AND functon. For example:
=IFS(E1=Y1,"",AND(E1=N1,B1>=14),"DUE")
Date1 ; 05/10/2023 4:00:00 AM
Date2 : 05/10/2023 6:30:00 AM
Result : how many hour ?
how to make formula ?
=DATEDIF(Date1,Date2,"H")
Hi! The DATEDIF function does not work with time. Try these recommendations: How to calculate time in Excel - time difference, adding / subtracting times. I hope this will help.
Dear Team,
I have a list of dates over a 5 year period, and would like to be able to add a column to show what day each date was, so I can filter data by day.
Apart from using filter, and chosing every 7 days for 5 years, is there any other way of doing this?
Thank you for any help
Aloha, I would like to create a formula that calculates the difference between dates that also stops calculating once action is completed.
I am calculating the amount of time it takes to process transactions, but I don't know how make the date stop counting once the action is complete. Any assistance is greatly appreciated.
Hello!
To prevent your date from automatically changing, you can use several methods:
1. Use Shortcuts to insert the current date and time
2. Use the recommendations from this article in our blog.
3. Replace the date and time returned by the TODAY function with their values. Copy the date (CTRL + C), then paste only the values using Paste Special or Shortcut CTRL + ALT + V.
Shabba beeling shoutout uptop snap