by When it comes to calculating dates in Excel, DATE is the most essential function to understand. As you probably know, Excel does not keep the year, month and day for a date, nor does it explicitly store weekday information in a cell. Instead, Microsoft Excel stores dates as serial numbers and this is the main source of confusion.
Not all Excel date functions can recognize dates entered as text values, therefore it's not recommended to supply dates directly in calculations. Instead, you should use the DATE function to get a serial number representing the date, the number that Excel understands and can operate on.
Excel DATE function
The Excel DATE function returns the serial number that represents a certain date. It has the following arguments:
Where:
Year - represents the year of the date.
Month - an integer representing the month of the year, from 1 (January) to 12 (December).
Day - an integer corresponding to the day of the month, from 1 to 31.
The DATE function is available in all versions of Excel 365 - Excel 2007.
DATE function - tips and notes
The DATE syntax looks crystal clear and straightforward on the surface. In practice, there may be some unobvious pitfalls that the below tips will help you avoid.
Year
Excel interprets the year argument according to the date system set up on your computer. By default, Microsoft Excel for Windows uses the 1900 system where January 1, 1900 is represented by the serial number 1. For more details, please see Excel date format.
- If the year argument is between 1900 and 9999 inclusive, Excel uses exactly the value you supplied to create a date. For example, DATE(2015, 12, 31) returns December 31, 2015.
- If the year argument is between 0 and 1899 inclusive, Excel adds the specified number to 1900. For example, DATE(100, 12, 31) returns December 31, 2000 (1900 + 100).
- If the year argument is less than 0 or greater than 9999, a DATE formula will return the #NUM! error.
Tip. To avoid confusion, always supply four-digit years. For example, if you input "01" or "1" in the year argument, your DATE formula will return the year of 1901.
Month
- If the month argument is greater than 12, Excel adds that number to the first month in the specified year. For example, DATE(2015, 15, 5) returns the serial number representing March 1, 2016 (January 5, 2015 plus 15 months).
- If the month argument is less than 1 (zero or negative value), Excel subtracts the magnitude of that number of months, plus 1, from the first month in the specified year. For example, DATE(2015, -5, 1) returns the serial number representing July 1, 2014 (January 1, 2015 minus 6 months).
Day
As well as month, the day argument can be supplied as a positive and negative number, and Excel calculates its value based on the same principles as described above.
Tip. At first sight, supplying negative values in the month or day argument of the Excel DATE function may seem absurd, but in practice it may turn out quite useful, for example in the complex formula that converts a week number to a date.
Excel DATE formula examples
Below you will find a few examples of using DATE formulas in Excel beginning with the simplest ones.
Example 1. DATE formula to return a serial number for a date
This is the most obvious use of the DATE function in Excel.
For example, to return a serial number corresponding to 20-May-2015, use this formula:
=DATE(2015, 5, 20)
Instead of specifying the values representing the year, month and day directly in a formula, you can have some or all arguments driven by of other Excel date functions. For instance, combine the YEAR and TODAY to get a serial number for the first day of the current year.
=DATE(YEAR(TODAY()), 1, 1)
And this formula outputs a serial number for the first day of the current month in the current year:
=DATE(YEAR(TODAY()), MONTH(TODAY(), 1)
Tip. To display a date rather than a serial number, apply the desired Date format to the formula cell.
Example 2. How to return a date based on values in other cells
The DATE function is very helpful for calculating dates where the year, month, and day values are stored in other cells.
For example, to find the serial number for the date, taking the values in cells A2, A3 and A4 as the year, month and day arguments, respectively, the formula is:
=DATE(A2, A3, A4)
Example 3. DATE formula to convert a string or number to a date
Another scenario when the Excel DATE function proves useful is when the dates are stored in the format that Microsoft Excel does not recognize, for instance DDMMYYYY. In this case, you can use DATE in liaison with other functions to convert a date stored as a numeric string or number into a date:
=DATE(RIGHT(A2,4), MID(A2,3,2), LEFT(A2,2))
Example 4. Adding and subtracting dates in Excel
As already mentioned, Microsoft Excel stores dates as serial numbers and operates on those numbers in formulas and calculations. That is why when you want to add or subtract some days to/from a given date, you need to convert that date to a serial number first by using the Excel DATE function. For example:
- Adding days to a date:
=DATE(2015, 5, 20) + 15The formula adds 15 days to May 20, 2015 and returns June 4, 2015. - Subtracting days from a date:
=DATE(2015, 5, 20) - 15The result of the above formula is May 5, 2015, which is May 20, 2015 minus 15 days. - To subtract a date from today's date:
=TODAY()-DATE(2015,5,20)The formula calculates how many days are between the current date and some other date that you specify.
If you are adding or subtracting two dates that are stored in some cells, then the formula is as simple as =A1+B1 or A1-B1, respectively.
For more information, please see:
Advanced Excel DATE formulas
And here are a few more examples where Excel DATE is used in combination with other functions in more complex formulas:
Using Excel DATE formulas in conditional formatting
In case you want not only to calculate but also highlight dates in your Excel worksheets, then create conditional formatting rules based on DATE formulas.
Supposing you have a list of dates in column A and you want to shade dates that occurred earlier than 1-May-2015 in orange and those that occur after 31-May-2015 in green.
The DATE formulas you want are as follows:
Orange: =$A2<DATE(2015, 5, 1) - highlights dates less than 1-May-2015
Green: =$A2>DATE(2015, 5, 31) - highlights dates greater than 31-May-2015
For the detailed steps and more formula examples, please see How to conditionally format dates in Excel.
How to make date formulas in Excel with Date & Time Wizard
Though DATE is the main function to work with dates in Excel, a handful of other functions are available to tackle more specific tasks. You can find the links to in-depth tutorials at the end of this article.
Meanwhile, I'd like to present you our Date & Time Wizard - a quick and easy way to calculate dates in Excel. The beauty of this tool is that outputs the results as formulas, not values. Thus you have a kind of 'two birds, one stone' opportunity - get the result faster and learn Excel date functions along the way :)
The wizard can perform the following calculations:
- Add years, months, weeks, days, hours, minutes and seconds to the specified date.
- Subtract years, months, weeks, days, hours, minutes and seconds from the specified date.
- Calculate the difference between two dates.
- Get age from the birthdate.
For example, here's how you can add 4 different units in cells B3:E3 to the date in A4. The formula in B4 is built in real-time as you change the conditions:
If you are curious to explore other capabilities of the wizard, feel free to download an evaluation version of the Ultimate Suite below which includes this as well as 60 more time saving add-ins for Excel.
I thank you for reading and hope to see you on our blog next week!
Available downloads
Ultimate Suite 14-day fully-functional version (.exe file)
680 comments
Great info that you have here. Please help. I am working on my sheet of manage data,I want excel to series easily for me
kindly help me
how can i put the series in this way
for example
01/2017
02/2017
03/2017
main thing is that 01, 02, and 03 is not date or month
this is my work sheet serial number i have alot of work sheet thats way i m manage one sheet for inform me to whos my work done or not
so kindly help me how can put serial
01/2017
02/2017
03/2017
04/2017
05/2017
continues....
Hi
I want to count a Today date in a particular cell,Suppose my all data in sheet1 and and i prepare a formula sheet on sheet2,in that i want a count from a cell Today date only(as Like so many date are there in that i want to count how many thing dispatch on Today date),so that directly reflect to my sheet2.
Hi there :)
What is the best excel formula to use, to calculate the actual date based on the xx number of days + a specific date, where its counted as day 1?
The formula I'm using currently, worked correctly on some dates, but not on others, it's one day less. Could this be due to the Stat holidays within the range?
For example,
a. 2 days starting from Aug 5, 17 = Aug 6, 2017
b. 3 days starting from Aug 5, 17 = should be Aug 7, but the same formula I used for "a" returned Aug 6
c. 7 days starting from Aug 5, 17 = Aug 11, 17, but returned Aug 10.
Please help! What am I missing....
how to date format change
ex: 01.01.2017
i need 01-Jan-17 format
Hello :)
Please let me know, How can I segregate the COLORED dates in to different columns.
Recently i did it manually but its very time taking procedure, please guide me to find an easy way.
Hi, I have written a macro to process dates as the X axis and the value as the Y axis. My challenges are that the length of column of dates could vary so some cells in the plotting range of the Macro could be empty (this returns default date in that cell of 01/01/1900. So my first question is how to use the VBA code so that the range is only taken with all cells containing a date and blank cells are ignored?
Next the macro will format the graph but i cant find a way to arrive at the X Axis minimum and Maximum Scale limits, if i use the "auto" function then the graph does not display until I reduce the Min and Max date number to either side of the actual date range. I thought i could use the DATEVALUE function to set the date value number as an integer and use that in the MIN and MAX graph formatting Axis code: "ActiveChart.Axes(xlCategory).MinimumScale = 42817" and "ActiveChart.Axes(xlCategory).MaximumScale = 42823". Replacing the number with the Integer for the start date and end date of my range.
Any suggestions on the above, I have searched a lot but not found the answer yet.
Trying to complete task that is completed by completion date with a total of percentage.
10 task starting at B2-B11 and dates are entered to show completed how can I get B12 to show total percentage completed??
A 2-12 has names of task
any help is appreciated
I want to know how to remove date formula to enter other date beyond a particular date example i want to entre 10-05-17 but formula is set for 07-05-17
A former co-worker created a spreadsheet for our reimbursement use but the calculating/formula fields are locked down and I don't have the password. I need to recreate this spreadsheet and one of the fields/formulas has me stumped.
Column A is a date column for manual entry by users. Starting at row 9 users enter a single date (04/25/2017). Single dates can be entered up to row 54.
The formula I need is for a period of time, based on the starting and ending dates found in column A.
This is what the locked field looks like with no data in column A: "From 1/0/00 to 1/0/00"
This is what the locked field looks like with data in column A, row 9 (1st row of data) & row 12 (last row with data): "From 04/24/2017 to 04/25/2017".
My TEXT formula is not correct
(=TEXT(A9,"mm/dd/yy")&" - "&TEXT(A54,"mm/dd/yy")
The result is this:
"04/20/2017 - 01/00/00" (even though I have data/dates in rows 10-17)
I'm not sure how well I have explained myself but any help is greatly appreciated. Thanks!
hi, pls i will like you to help me with the steps on how to convert text into numerical values Using Excel 2010.
Hello,
I am attempting to calculate the inventory value based on the inventory quantity (in column B) and today's unit price (columns C through I represent the days of the week and have unit prices below each day for each item; I have a new sheet for each week). I want the inventory value to display in column K following each type of item in my inventory (column A for each item).
Hello,i please need assistance.We award employees at 5 year intervals.Please help with formula to use.
Hello, Spinky,
If the starting date is in A1, then you place the following formula in A2:
=DATE(YEAR(A2) + 5, MONTH(A2), DAY(A2))
Check the link to see DATE function in more details.
Hello please help. What is the calculation for this. I want to get the average and/or sum.
ColumnA: 15 days, 1 hours
ColumnB: 15 days, 6 hours
Thank you
Hi,
I got a calculation to do in excel and I am completely stuck. I need to calculate if a staff is eligible for local leave or not. The staff is entitled local leave after 1 year (365days). So, if I have a date of entry 9/04/2017 in the next cell it should say YES if not 'NE' Not Eligible.
Can someone help me on this please?
Br,
John
Hello, John,
assuming that the dates are in A row, you need to calculate the following: date of hiring (A1) – date of entry (A2) = result (A3).
Try the next formula in A3:
=IF((A2-A1)>=365, "YES", "NE")
hi, can you help me with some date formula, i wanted to put formula in cell A1 where when you write anything on cell B1, the cell A1 will automatically generates a date for that day.... and on the next day, when you write on cell B2 the the cell A2 will automatically generate a date on the same day but will not affect the cell A1... meaning if today is January 1, 1900 and write anything on cell B1, cell A1 will be dated January 1, 1900 and when i write tomorrow on cell B2, cell A2 will be dated January 2, 1900 but cell A1 will still be January 1, 1900.... hope you can help me.
Hello,
I have a table with formulas containing dates in them. Is there a way that these cell formulas automatically update with new results every time I put in new weekly dates in a separate column?
(Example: Columns S6 till S12 - Sunday, 3/26 till Saturday 4/01)
I figured out how to calculate dates based off of a start date, however, when that initial cell is blank, it fills in random dates that I don't want to be visible. Is there a formula that I can embed into the cell so that they stay blank till the initial cell is filled?
Hi, Johanna,
if the initial cell is A1, try the following:
=IF(ISBLANK(A1),"",FORMULA)
Note, that instead of the word FORMULA you put that formula of yours to calculate the date.
Hi
i'm looking for a formula to get service years on a specific date. For example, joining date 01/01/2015 in cell A1, what is the formula to get years on 31/12/2016 ?
Hello Everyone
I've been looking everywhere for help to my edate error but cant find anything that works. Hope someone here can help.
I am trying to use edate to subtract 12 months from a given date to use in a look up calculation for Fiscal YTD results. Everything works fine until February 2017 rolled around and the results give me February 28, 2016 not February 29, 2016. Which causes a #N/A error.
=IF(B8=EDATE(Q3,-12),B10,SUM(B10:INDEX(B10:Y10,1,MATCH(EDATE(Q3,-12),B8:Y8,0))))
Is the formula I am using which works fine for every other month.
Q3 refers to the current month end date: in this case: 2/28/2017
Row 8 has dates in subsequent cells as follows:
7/31/15 8/31/15 9/30/15 10/31/15 11/30/15 12/31/15 1/31/16 2/29/16 3/30/16 etc all the way up to my 2/28/17 month.
the formula returns: 2/28/16 not the 2/29/16 I need to get my prior fiscal year to date total.
In other words my formula returns the date as 42428 not 42429. Which the causes the formula to error out since there is no 42428 in the date cells.
Thanks in advance for the help.
HELLO DEAR
I WANT SHOWN DATE IN EXECL IN BELOW PATTERN
12/05/2016
I WRITE 120516 AND ITS SHOW AS 12/05/2016