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
Hi,
I am working on a spreadsheet where I have to update number of Fridays in the month based on the current date.Currently,I am using the formula COUNTIF($C$11:$C$41,">0")-0.167*0 where I update "0" manually depending on whether its the 1st Friday, 2nd etc. Can someone assist to do this manual input automatically. The ($C$11:$C$41,">0") counts the number of days that the plant produced.
Adding days to a date:
=DATE(2015, 5, 20) + 15
The formula adds 15 days to May 20, 2015 and returns June 4, 2015.
my question is:
how i want add 15 workdays(exluded sunday and saturday).. help me
Hello, Arman,
Please try the following formula:
=WORKDAY.INTL(DATE(2015, 5, 20), 15, 1)
You can learn more about calculating weekdays in Excel in this article on our blog.
Hope you'll find this information helpful.
Hi,
I want to be able to add an increase to a salary if someone was employed between 2 dates for example:-
if someone joined after 1st March 2012 and before 1st Jan 2018,increase cell B5 by 1
Thanks
Hello sir,sir which formula used for all company employee experience in age between 0to5,5to10,20to30 in excel
Hi, I want to highlight day 15 of every month between years 2019 - 2020 in a single row on a Gantt chart. Any suggestions about how to do this?
I am using formula index
i have two sheets, and in the sheet 1 for the data on transactions per each have the transactions and second sheet i am using the formula
=IFERROR(INDEX(Table1[Column2],MATCH($C$2,Table1[Column1])),"No Data")
and i am facing the problem on match $C$2, (this one i am using match with month) how i can write all date 1 to 31 (month)
C2 i am using 31-01-2018 and result only the particular date, i need whole month data
Hi , can someone help me with the following please ? I need to calculate the re-test date for students depends on their score ,
e.g. If students score more than 80 marks he must retake his test within 3 years max, if score between 30-80 he must retake his test within 2 years , if score lower than 30 he will retake his test within 1 year ...
The date of the test is in column B, Score in column C and I need to calculate the date of the re-test in column D .
( it's not that simple in real life but for this purpose I had to simplify so you guys understand :) )
I would really appreciate any help whit what formulas to use . Thank you
25/10/2018 how to make appear like 25 Oct 2018 in excel cell
Normally 10/25/2018 is appear as 25 Oct 2018 in excel cell.
Suthan:
You can modify how the numbers and dates appear in the cells by changing the cell's formatting.
Right click on the cell select Format Cells then Date and choose a date format from the list then click OK.
How to get the formula of 30 DAYS OVERDUE if the given is date today, terms and due date ?
Steve:
What are the terms and due date?
Hello There,
You can use below formula:-
DATE(YEAR(A1),MONTH(A1),DAY(A1)+30) for 30 days.
Hii Concern,
I just wanted a formula for example,
ending date of one card is like 25/08/2018 and one more card for the same person expiry date is like 26/09/2018 and the output i need is like
by adding +60 days for first card and by adding +30 days for second card i need output is which ever less date will comes that date i need as output,
can someone pls suggest.
Hi,
I would like to know the formula to get the below requirement
If i type 2018 it should provide the date in this format 1 Jan 2018 to 31 Dec 2018
if i type 2017 the result should be 1 Jan 2017 to 31 Dec 2017
Want to formula
0-30 days 2%
30-60 days 1 % and
60-90 days 0 %
I was wondering if you could help me with a calculation. I need to be able to calculate 14 hours out of every 24 hour period from dates and times entered in 2 cells. For example cell A1 would have 4/10 13:00, cell B1 would have 4/12 18:00. I know how to do the total hours calculation, but we only pay for 14 hours out of every 24 hours with a 2 hour and 6 hour grace period. I need a formula to do this. The total hours would be 53. So I would need cell D1 to = 31 and cell E to = 28 For D1 we would need the calculation to show 4/10 15:00 - 4/11 15:00 = 14 hours paid, 4/11 15:00 - 4/12 15:00 = 14 hours paid, 4/12 15:00 - 4/12 18:00 = 3 hours paid...for a total of 31. For E1 it would be 4/10 19:00 - 4/11 19:00 = 14 hours paid, 4/11 19:00 - 4/12 18:00 = 14 hours paid...for a total of 28. Can this be done or is it possible to create a macro that would do this?
I am working on a spreadsheet that I am using the following formula. =COUNTIFS(REWORK!C:C,"BOM Error",REWORK!A:A,"=12/17/17") How can I incorporate the date function into the formula to avoid having to try to change for each line?
=COUNTIFS(REWORK!C:C,"BOM Error",REWORK!A:A,"=12/17/17")
DEAR,
IF I HAVE 03 CYLINDERS CY 01, CY 02 & CY 03 IN STORE. I WANT TO TRACK WETHER THE SAID CYLINDER IS FOR HYDRO TEST OR IN O.K. CONDITION. THE CYLINDER SHOULD BE HYDROTESTED AFTER 06 YEARS FROM THE DAY OF MANUFACTURING OR HPT TEST.
E.G.
CY 01 (01.01.2016) THE RESULT SHOULD COME O.K.
CY 02 (06.01.2005) = FOR HPT (BECOZ THE CYLINDER HAS PASSED 06 YEARS FROM THE DATE OF MANUFACTURING & SHOULD BE HYDRO TESTED)
CY 03 (05.07.2009) FOR HPT
THANKS IN ADVANCE
Shams:
Here is how I set this up:
CYNum Date Tested Current Date Result
CY 01 1/1/2016 5/4/2018 OK
CY 02 6/1/2005 5/4/2018 HPT
CY 03 5/7/2009 5/4/2018 HPT
CYNum is in A
Date Tested is in B
Current Date is in C
Result is in D
Notice each variable is in a separate column and cell.
Notice date is in M/D/Y format.
Current Date in each D cell is function =TODAY()
In Result cell D2 for 1/1/2016 the formula is:
=IF(DATEDIF(B2,C2,"Y")>=6,"HPT","OK")
How to convert (hex) to (day,month,year).
D11-M12-Y16 - time 23 :22 : 48 - hex 62 69 E7 C8
D12-M12-Y16 - time 04 :35 : 16 - hex 62 6A C0 84
D12-M12-Y16 - time 05 :26 : 40 - hex 62 6A CC 90
D13-M12-Y16 - time 11 :46 : 22 - hex 62 6E B5 0E
D13-M12-Y16 - time 11 :48 : 33 - hex 62 6E B5 91
Dears,
I have a difficult question.
I have a duplicated codes with different receipts dates.
How can I get the latest date for each code
Shady:
The simplest method would be to sort the data by date.
Can you try that?
How to convert (hex) to (day,month,year)-D11-M12-Y16, 08:43:25.
Hex (0X62 0X68 0X8A 0X2D).
Here is the data format.
T4 - encoding 1:
1. word 3*(365*(Year-1986) + DayNumberInYear) + Hour/8
2. word 3600*(Hour modulus 8) + 60*Min + Sec
DayNumberInYear assigns 1 to January the first and increments
the number by one each day, treating leap-years correctly.
Unfortunately, this encoding assigns the same 1. word to December 31. in a
leap-year and January 1. in the next year.
T4 - encoding 2:
1. word 3*(366*(Year-1994) + DayNumberInLeapYear) + Hour/8
2. word 3600*(Hour modulus 8) + 60*Min + Sec + 32768
DayNumberInLeapYear assigns 0 to January the first and
increments the number by one each day, assuming all years to be
leap-years.
Here's an old Lotus 1-2-3 method that works great in Excel and will accomplish what you're looking to do:
=DATEDIF(D2,E2,"y") &" years,"&DATEDIF(D2,E2,"ym") &" months," &DATEDIF(D2,E2,"md") &" days"
Enter this formula into F2 and it will show the number of years, months and days between the start date and the end date. It looks like this: "3 years,8 months,28 days".
If you need to sum the years, months and days you may need to enter them into separate columns. If so, just enter the DATEDIF(start,end,"Y") pieces in separate columns minus the "&"Years", etc. part. You'll have only the numbers that you can then sum.