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
I am trying to caculate the years a person has worked for company to a particuler date. Example is April 3, 2014 to December 31, 2017. D2 is the hire column and E2 is the ending date. I am needing to calculate the yrs, months, days worked in to column F2. I will also need to tally up the whole column of F2 for a grand total of the employees.
I have a table with hundreds of invoices. some are paid in 30 45 or 60 days.
I am trying to calculate the expected date that the invoice will be paid.
amount is in b3 payment days is in c3 and I have next 75 days in columns f3 through the next ten columns.
How do I write the formula to have the amount drop into the correct week?
thanks in advance.
Thank you in advance for your help!
I am trying to do a summary sheet using cell values from sheet 2. We are calculating number of dogs or cats (2 different columns on the summary sheet so cats will be a separate formula - but they are all mixed together on sheet 2). But I want totals for the month so made that another condition. So 2 IF conditions - column F should equal dog and column A should be between two dates inclusive. I think I have the formula close here but it doesn't like the formatting of my dates. I have tried a few different ways - DATEVALUE, d/m/y, no spaces, quotations. Can you help? Thank you so much!!!
=COUNTIFS(AND(Sheet2!F2:F1000=Dog, Sheet2!A2:A1000>=DATE(2018, 1, 1),Sheet2!A2:A1000<=DATE(2018, 1, 31)))
Kindly reply with a solution to:
(1) I creat a workbook for each month (30 worksheets) and have to enter date manually one by one at left hand corner to each worksheet e.g. 01-Nov-2018; then next work sheet will be 02-Nov-2018. Is there a way I can enter the formula in the first worksheet and it will give me date in sequence on the rest of the month worksheets?
(2) The Tabs (name) for each day worksheet also need to be changed manually e.g. 01 Nov AM, 01 Nov PM, 02 Nov AM, 02 Nov PM ...
Thank you
I'm trying to calculate dates in excel. Could someone help me with the solution for this?
I want the single formula to be calculate automatically. Do we have any formula for this?
Example: Today’s Date is 25 mar 2018
Sl. Order Date Sent date Overdue Days
1 22 Mar 2018 25 Mar 2018 3
2 22 Mar 2018 Not Yet Sent 3
3 20 Mar 2018 25 Mar 2018 5
4 10 Mar 2018 Not Yet Sent 15
Thank you in advance
plz help
when in cell a1 have only year and b1 have complete date, how can we use subtract formula for it.
for example
a1 b1 Result of subtraction
1967 07/03/2018 ?
Hello Hayat Khan
use this formula i will make sure there formula is work.
=DATE(YEAR(D14)-C14,MONTH(D14),DAY(D14))
D14- mention Year Cell Range
C14- mention Date Cell range
Is it possible to know the difference between 2 dates if the date format is January 31 2018?
If yes, can help on the formula?
Hey guys,
I'm trying to calculate range in excel. Could someone help me with the solution for this.
I want the range to be calculated automatically. Do we have any formula for this?
example:
Sl. Employee Years Range
1 Abc 5 5 - 6
2 DEF 8 8 - 9
3 GHI 5 5 - 6
4 JKL 5 5 - 6
I am trying to get the days of the week in column A for the whole year but skip Saturdays and S, is there a formula for this?
I have a question.
I have a formula that pulls a date from another worksheet.
=IF(Datasheet!M2"",TEXT(Datasheet!M2,"mmm-yy"),"Jan")
In the cell adjacent to that "second date formula" I have a formula that creates the next month.
=DATE(YEAR(Datasheet!$M$2),MONTH(Datasheet!$M$2)+1,1)
Below each date I have a formula like this.
=SUMPRODUCT(--(TEXT(Homeloans!$A$1:$L$1,"mmm-yy")=B$1),Homeloans!$A$2:$L$2)
That pulls a value from the homloans worksheet.
The problem is the "second date" formula will not allow me to pull the value from homeloans because of the way it is formatted.
Any suggestions on how I should format the "second date formula" and it still create a dynamic month based on the first month in the series?
Thanks
Hi the below may be helpful to you.
=IF(DAY(E8)15,EOMONTH(E8,0)+1,IF(DAY(E8)>15,EOMONTH(E8,0)+1,"")))
Replace E8 with your active cell reference
I have a doozy for you. based upon the date entered into column A, I want a formula to determine if that date is on or before the 15th of that month. If the date falls within that range then I want it to return a value for that month and year in column B. If the date in column A is after the 15th of that month then I want it to return a value in column B that is for the next month and year. For example: If someone entered 6/2/2017 in column A, then column B would return a value of 6/1/2017. If someone entered 6/16/2017 in column A then column B would return a value of 7/1/2017 in column B.
Thanks so much. Great forum!
Hello,
Please try the following formula:
=TEXT(IF(DAY(A1)>15,DATE(YEAR(A1),MONTH(A1)+1,1),DATE(YEAR(A1),MONTH(A1),1)),"mm/dd/yyyy")
Hope it will help you.
The date imported displays mmmyy (Jan18) for Jan 2018. I need that to display 01/31/2018. How do I do that?
Thank you in advance!
are you trying to show the 31st / last day of the month as the output? For example do you want to see: 1/31/2017, 2/28/2017, 3/31/2017...?
ANX171022
what formula i should use to get the manufacturing date and expiry date from the above number
ANX=Product name
Manufacturing expiry
17=2017 2020 (3 years from mfg)
10=Oct oct
22=Date NOT NECESSARY
Hello,
If I understand your task correctly, you need 2 different:
For manufacturing date:
=TEXT(DATE("20"&MID(A1,LEN(A1)-5,2), MID(A1,LEN(A1)-3,2),MID(A1,LEN(A1)-1,2)),"yyyy Mmm")
For expiry date:
=TEXT(DATE("20"&(MID(A1,LEN(A1)-5,2)+3), MID(A1,LEN(A1)-3,2),MID(A1,LEN(A1)-1,2)),"yyyy Mmm")
Hope this will help you!
please give the solution that subtract the date from 05-12-2016 to 06-04-2016, but the result should be 275 days (30 of April+31 of May + 30 of June +31 of July + 31 of Aug + 30 of Sep + 31 of Oct + 30 of Nov +31 of Dec), which is received from the end date of Dec 016 to first April 2016 (ie) 31-12-2016 (-) 01-04-2016
Please explain with support of EXCEL Function
S.Arivananthan
Hello,
Please try the following formula:
=DATEDIF(EOMONTH("06-04-2016",-1),EOMONTH("05-12-2016",0),"D")
Hope it will help you.
Looking for a way in excel to make cells that auto populate the fiscal week number. For example I put in column A3 "Week 45" the I want the next row underneath to auto populate to "Week 44".
I'm trying to make a tracker, I need to cell the cells color when the typed in date 60 and 30 day (amber) and 30 to 00 days (red) from the one year mark. can anyone help me?
How to increment the date for a week if we have particular condition as text using excel formula
Hello, Spandana,
For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
Please also don't forget to include the link to this comment into your email.
I'll look into your task and try to help.
>30 days from today column must show "0". how to make formula.
Oh thank fuzzy kittens that I found you. If you can help it will be much appreciated. I need a column to show the fiscal year and week number. I actually need two formulas:
Formula 1- calculate just the fiscal year, if the year starts Oct 1 and ends Sept. 30 (So if the date entered was Oct. 13, 2017 I'll get FY18 as a result). Next I need a formula that will
Formula 2 - Calculate the week number if the year starts Oct. 1 and ends Sept. 30
I have these two formulas, but they don't seem to work and I don't know how to fix them.
FY Formula - =INT(([A1]-DATE(YEAR([A1]),1,10)+(TEXT(WEEKDAY(DATE(YEAR([Date of Engagement]),1,10)),"d")))/7)
Week # Formula - =INT(([A1]-DATE(YEAR([A1]),1,10)+(TEXT(WEEKDAY(DATE(YEAR([A1]),1,10)),"d")))/7)
If you can help me with this I would most appreciate it.
Thanks!!