Using DATE function in Excel - formula examples to calculate dates

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:

DATE(year, month, day)

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)
Using the DATE function in Excel to get a serial number representing a date

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)
Excel DATE formula to return a date based on values in other cells

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))
DATE formula to convert a string to a date

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.

DATE formulas to add and subtract dates in Excel

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
Using Excel DATE formulas in conditional formatting

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:
Subtract dates in Excel using the Date & Time Wizard

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)

Table of contents

680 comments

  1. Hi, can you help me? I want to design a column that if the date encoded is today, the color will change into green. If it's past today, change to red. If it's within this week, yellow. Something like that. I hope someone can help.

  2. If in excel sheet coloum a mentioned as a feb 22,2021 so how we get in cloum b as a day name.

  3. Hello, hope you could help me, with this
    Target Date Date Submitted %
    January 25, 2022 February 2, 20221 ????

    How can I get their rate of their submission?
    Thanks

      • Thank you for your reply,

        I am getting their performance in %
        They have their target dates to submit their data.
        Their Target is January 27, 2022 and If they submitted on February 17, how can i get their absolute performance. This is part of their performance evaluation review.

        Thanks

  4. Hi,

    I am trying to find out how to use a formula to find how many products have been purchased between two dates and then spilt them within products purchased (3 columns of products but i have not figured this part of the formula out yet).

    I've tried to use the following formula but it doesn't seem to work: -

    =COUNTIFS(E2:E88,">="&I94,F2:F88,"<="&I95)

    Please can you guide me.

    Kind regards

    Saiqa

    • Hi!
      I’m sorry but your description doesn’t give me a complete understanding of your task. I can't guess what doesn't work in your formula. The formula is written correctly, but it is not known what data you have. I guess this article will help you: Count unique values with criteria
      If this is not what you wanted, please describe the problem in more detail.

  5. I am trying to create a formula that will allow me to determine when someone is expected to have something done by. Example. they start of Sept 10, 2021 and they must have a specific course completed within 60 days of the start date. Everyone one has a different state date and I must know how much time remains before the 60 days is up.

  6. Can you help me to convert this format 3.2021 (Month.Year) into 1-Mar-2021 please

    • Hello!
      You need to convert text to date. The formula below will do the trick for you:

      =DATE(RIGHT(A1,4),LEFT(A1,SEARCH(".",A1)-1),1)

      Set the date format you need as described in this guide.

  7. Hi im trying the following, can you help.
    e.g ill write it phonetically
    if today = January the sum A1
    If today = february then sum A1 +b1

    its for adding up monthly targets

  8. I have the formula set as =DATE(YEAR(H2)+M2,MONTH(H2),DAY(H2)) and it is working in most instances where the date is the number in M2 is the number of years to add (i.e. the same date in 6 years time) .

    I need a way to modify the formula so that the date shows a different date instead.
    Basically, if the date in H is 07/08/2021 and the number in M is 6 AND it is an audit-able entry then I need the date formula to calculate our audit date plus 6 years which is 31 March (i.e. 31/03/2028 instead of 07/08/2027.)

  9. Hi I have a question and cross my fingers if you can provide any help.

    The 1 spreadsheet has daily data (B column) with date in the form of dd/mm/yy (e.g 1/1/2018....16/08/2021) (A column).
    The 2 spreadsheet has only years (2018..2021) (A column).
    I want to use a formula that will match the data that is the latest date of each year to the 2 spreadsheet to the respective year. (B column in 2nd spreadsheet) .

    Thank you in advance. If you have any advice i am grateful .

  10. Hi I have a question and cross my fingers if you can provide any help.

    I have two spreadsheets with dates and data

    The 1 spreadsheet has daily data (B column) with date in the form of dd/mm/yy (e.g 1/1/2018....16/08/2021) (A column).
    The 2 spreadsheet has only years (2018..2021) (A column).
    I want to use a formula that will match the data that is the latest date of each year to the 2 spreadsheet to the respective year. (B column in 2nd spreadsheet) .

    Thank you in advance. If you have any advice i am grateful .

  11. I have 2 definition of dates in one cell which is the Post dated and next working day, what formula can I use if I choose post dated on a cell and then the other cell will automatically choose same day and same as the next working day..thank you

  12. How to do the formula of the following example;

    Transaction Date Type of Customer Due Date
    20-July-2021 Regular 4-Aug-2021 Add 15days if regular
    20-July-2021 Consignment 27-July-2021 Add 7days if consignment

  13. Hi what formula can i use for countif if i want it to count items completed on a specific month. data on excel are dates. here is what i tried kindly let me know how to modify countif(Sheet8!I2:I222,">" & date (2020,8,1),"<"& date(2020,9,1))

  14. Hello, i need a help with a formula to automatically add a due date based on an assignment date on column A (1 Jan 2021) and a text in column B (Red) then column C to have a due date 90 days from the date on column A (1 Jan 2021.

  15. I am trying to figure out a formula for the following:

    If anything in (A) column=5, then take dates in (B) column And add 14 years, 1 month and subtract 1 day, And if anything in (A) column=4, then take dates in (B) column And add 8 years, 1 month and subtract 1 day.

    Any help would be appreciated

  16. Hello, I need the formula which will produce a future date when I input a number of weeks
    Column A: Date
    Column B: Lead time (number of weeks)
    Column C: I need the future date in here

    Thank you!

  17. ACTIVITY_FROM ACTIVITY_TO SPOT_STATUS Days total amount
    2/5/2021 4/5/2021 RO Printed 60 200000
    i want month of Feb how much ?
    I want month of Mar how much ?

      • Data sample-
        If i have joined any company from 13 Jan to 8th July 2020 and there is monthly salary is 15000
        then how to calculate 19 days for Jan also Feb/Mar/Apr/May/June/and 8 days July salary?

        Column ref-:
        A= 13 Jan 20
        B=8th July 20
        C= 15000 Monthly
        Now need to calculate in Monthly-:
        D= Jan month ?
        E=Feb month?
        going forward till 8th july
        Thanks
        Purna

        • Hello!
          If cell A1 contains the start date of work (for example, January 13), then use the formula

          =15000*(EOMONTH(A1,0)-A1)/(EOMONTH(A1,0)-EOMONTH(A1,-1))

          I hope this will help, otherwise please do not hesitate to contact me anytime.

          • Great thanks worked this function.

  18. I need a formula to look at two different dates in two cells and choose the latest date of those two to then add 26 weeks to and come up with a new date.

  19. I have company 's code no in column A. I have one date i. E. 01/04/2018 in column B. There is range of date. 01/04/2015,01/04/2016,01/04/2017,01/04/2018,01/04/2019 in column C. In column D, there is range of companies code no. I. E ABC, BCD, CDE, DEF Now I need to match particular company' s code and particular date with range of dates and company's code. How to do this.
    Pl guide me.

  20. Doc Due Mode Premium Recevable Receved Recevable Receved
    1/20/2020 5/20/2020 M 2000 5 4 10000 8000
    1/20/2020 7/20/2020 Q 5000 3 2 15000 10000
    1/20/2020 7/20/2020 H 10000 2 1 20000 10000
    1/20/2020 1/20/2021 Y 200000 2 1 400000 200000

    Plese can you send me What is the Excel formula (Received /recevable) ammount

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)