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.

What the Excel DATE function does is return the serial number of a specified date. It has the following arguments:

DATE(year, month, day)

**Year** - represents the year of the date. 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. In this date system:

- If year is between 1900 and 9999 inclusive, exactly that value is used for the year. For example,
`=DATE(2015, 12, 31)`

returns December 31, 2015. - If the year argument is between 0 and 1899 inclusive, Excel calculates the year by adding the specified number to 1900. For example,
`=DATE(100, 12, 31)`

returns December 31, 2000 (1900 + 100). - If year is less than 0 or greater than 9999, a DATE formula will return the #NUM! error.

**Month** - an integer representing the month of the year, from 1 (January) to 12 (December).

- If month 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 month 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** - an integer corresponding to the day of the month, from 1 to 31.

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.

The DATE function is available in all versions of Excel 2019, Excel 2016, Excel 2013, Excel 2010, and lower.

Below you will find a few examples of using DATE formulas in Excel beginning with the simplest ones.

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)`

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)`

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))`

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) + 15`

The formula adds 15 days to May 20, 2015 and returns June 4, 2015.

**Subtracting days**from a date:`=DATE(2015, 5, 20) - 15`

The 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:

And here are a few more examples where Excel DATE is used in combination with other functions in more complex formulas:

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.

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 a 14-day trial version of the Ultimate Suite 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!

Excel formulas
CSV
Excel functions
Print
Financial functions
Vlookup
Merge data in Excel
SumIf
CountIf
Excel Compare
If statement
Excel Charts
Pivot Table
Conditional formatting
Excel formatting
Excel duplicates
Excel date
Excel tips
Excel macro
Outlook
Google Sheets
Outlook duplicates
Outlook templates

## 461 responses to "Using DATE function in Excel - formula examples to calculate dates"

Hi,

I am trying to create a spreadsheet that alerts me to the approaching time limit of some applications.

I need a formula that will use the "valid date" in column A and "decision date" in column E to tell me via colour code:

* Red - 0-7 days from valid date to decision date

* Amber - 8-24 days from valid date to decision date

* Green - 25-100 days from valid date to decision date

I've tried a few variations of things with no luck and it's driving me insane!!

Thanks in advance

My completion date should not chnage from previous date to current date. i want a fromula to track how many docs an executive has completed yday, today and so on..I have used now fromula as well but it chnages the date to present.

Hello I am unable to change date format from MM/DD/YYYY to DD/MM/YYYY please help me

Thank you for the info!

Hi

I need a formula to calculate employees leave days "to date"

How would I separate birthday column of 07/06/48 to a separate column of month, day, year?

Thank you.

separate 07/04/1959 to 07, 04, 1959 per column

I wanted to create an Excel formula that produced an 18th birthday based on a date. Through trial and error, I came up with this one.

Cell A1 = 4/26/2008

Cell B1: =DATE(YEAR(A1)+DATEDIF(A1,TODAY(),"y")+(18-ROUNDDOWN(((TODAY()-A1)/365),0)),MONTH(A1),DAY(A1))

Result = 4/26/2026

I am trying to create a formula to find the departure date from a loading date...for eg if loading date is 2nd December and departure for that specific country is Tuesday/ Friday then the departure date should be displayed as 3rd December. if loading date is 4th December then departure date should be 6th December Friday. I have 1 column with the country which is vlook'd up from another sheet in the workbook along with loading dates

I am trying to create a schedule in table format where the date format is ex (Sunday, Dec 02)and want it to autofill across the top. I also need this to be able search by day or date for an app.

Hi Can you help.

I am trying to track absence and using =DATEDIF(D62,$N$2,"d") to give me the total of days someone if off sick, however I need it to stop counting when they return to work. Any ideas?

Thanks,

Steve

I have a question.

Im new to excel and my boss wants me to calculate Day,month and year from a different cell.

and it's about retiredate. I have to calculate from their birthdate to the next 60 years. I mean I can use this formulas [=date(year(cell)+60+if(month(cell)>=10,1,0),9,30)] but just in case their birthdate is in the same cell. but from the different cell I can't. I really don't know what to do with this. I really appreciate if someone can help me.

***sorry for my english***

Hi

i am trying to get a yes or no answer for:

Employee's hire date is greater than 3-31-2019, yes or no

but an employee hired on 1-2-1990 is showing as "no"

please help

I WOULD LIKE TO DRAFT AGEING REPORT USING EXCEL.

WE PROVIDE LOANS, I WOULD MY EXCEL TO HIGHLIGHT A DAY BEFORE THE DUE DATE

Hello

I am trying to create a table whereby I set one anticipated start date and this can then autofill other cells by putting in how many weeks after (eg. start date is 05.03.20 so if I put a 2 in the weeks column the date would show up as 19.03.20). Does anyone know if this is possible/how to do it?

Many thanks

Ella.

How can I from say cell N11 Up until I11 count all the days that art between as a total amount of days that have passed so far does my question make sense?

I have a start date and a end date but as the days in between get added in as time goes on they need to be subtracted from the amount of days in my days *total column*.

*Start* *End* *Total Days for project*

03/03/2020 dates dates dates 05/05/2020 Days passed/left?

I'm trying to look at a priority level = A (+2 days), B (+5 days), C (+10 days) or D (+20 days) (in column E) then add the corresponding number days (+# days to column K) from the date in column C. Example in column C I have "01 Jan 20" and in column E I have "B" therefore I want column K to automatic insert "06 Jan 20" which is the +5 days due to priority level "B" selected. Thank you in advance for your assistance.

Hello Ken,

Please try the following formula:

=C1+CHOOSE(MATCH(E1,{"A","B","C","D"},0),2,5,10,20)

I hope it’ll be helpful.

I want to create remarks that denote that a particular activity is due in certain days (based on the last date when it was performed, mentioned in the sheet) or a particular activity has passed *this* number of days past its due date (also mentioned in the sheet)

The sheet has current date, activity performed date and due date in columns. Kindly help

Hello Swaleha!

If I understand your task correctly, the following formula should work for you:

=IF(B1="","Performed in "& (C1-A1) & " day", (A1-B1) & " days ago")

If there is anything else I can help you with, please let me know.

Hello team,

Need help... How to calculate the collection date when 57 days credit term starts from the end of each transaction week?

Invoice date is April 1, 2020

Hello!

I’m sorry but your task is not entirely clear to me. What is "transaction week"? We are not experts in finance and credit. Therefore, we can not understand your terms.

For me to be able to help you better, please describe your task in more detail. Please let me know in more detail what you were trying to find, what formula you used and what problem or error occurred. It’ll help me understand it better and find a solution for you. Thank you.

need a formula where we have a due date and mail out date, the due date will determine if it's past due or current and the mail out date will determine if paid, under a status column, cannot figure out how to have 2 IF functions that helps with the 2 logical test that can give us these 3 statuses, paid, past due, current.

I am trying to make a chart with conditions that applys a date plus a number of days if a cell states yes in a new cell. I cant think of a proper formula. Can anyone help?

Hello!

Use the section in this article above - Example 4. Adding and subtracting dates in Excel. Learn also Using Excel IF function with dates

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

Hello I am unable to change date format from MM/DD/YYYY to dd/MM please help me

Hello!

I think this article will help you.

Hope this is what you need.

Hi , i am working on a file where i want to get Today,Year,PrevExpiry, PrevExpiryStart, currentExpStart,CurrentExpiry,Expiry to calculate dates

Whereas i am having Expiry dates for a year. Expiry end on Thursday of every month, so Next day,ie Friday will be Start day for expiry.

The problem is working days are Five in the week and there is holidays and if expiry date is holiday then it will take previous day and if it Last day of the month then it should take first day of the next month.

Which is not coming in my formula.

=DATE(YEAR(Today),MONTH(Today)-1,0)+MOD(-WEEKDAY(DATE(YEAR(Today),MONTH(Today)+3,0),2)+1,-7)..where i am missing

contract start 01-10-2019

contract end 31-08-2020

how many days remaining for renewal

Hello!

If I understand your task correctly, the following formula should work for you:

=C2-TODAY()

or

=DATEDIF(TODAY(),C2,"d")

C2 --- 31-08-2020

I need assistance please. I am working on a spreadsheet with a Surname in Column D; Admission date I; Discharge date K; Date back at work L; Deceased Date M; Healthy/Deceased N;

My formula: =IF(D3="","",IF(N3="Deceased",(_xlfn.DAYS(M3,I3)),(IF(L3="",(_xlfn.DAYS(TODAY(),I3)),_xlfn.DAYS(L3,I3))))) So it means if there is a Surname in D, Look in M if deceased; if deceased calculate Deceased date, if not deceased calculate the Days off sick. What am I missing or doing wrong? I only get the #NAME? Back.

Hello!

I noticed the smart quotes in your formula and I suppose they are the cause of the error. Please convert your smart quotes into the straight quotation marks, that should fix the problem. Also, you may check this Microsoft manual to learn how to disable smart quotes: https://support.microsoft.com/en-us/office/smart-quotes-in-word-702fc92e-b723-4e3d-b2cc-71dedaf2f343

Check the formula. I have this formula

=IF(D3="","",IF(N3="Deceased",(DAYS(M3,I3)),(IF(L3="",(DAYS(TODAY(),I3)),DAYS(L3,I3)))))

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

Hi. I am trying to calculate future dates from today using a given date in the past such as given date = 6/1/20, today 7/6/20, the fixed day of month (excluding weekends and holidays) is the 1st of each month so I need my formula to tell me the next date from 7/6/20 (in this situation would be 9/1 then 10/1, etc) and to continue for a given period of time. I have the following formula: =WORKDAY(EDATE($B$6,2)-1,1,Sheet2!B1:B14) but it will give me the next month based on todays date. Help please

Hello Angela!

If I understand your problem correctly, you want to determine the first working day of each next month. The formula below will do the trick for you:

=WORKDAY.INTL(EOMONTH(A1,0),1,1)

I hope this will help

I have a spreadsheet with completion dates. For Example:

2/21/19

5/24/20

11/28/19

I need to calculate the percentage of how many I have that are less than 365 days. How would I go about doing this?

Hello!

If I understand your task correctly, the following formula should work for you:

=YEARFRAC(DATE(YEAR(A1),1,1),A1)

I hope my advice will help you solve your task.

Hi,

I want to make a spreadsheet of cut off dates which will keep changing every year as the sheet is opened. How will I get this? Whats the excel formula?

Hello!

Perhaps this article will help you

=IF(AND(TEXT(B3,"HH:MM")="09:30")=TRUE,B3,IF(TEXT(B3,"HH:MM")>="15:40",DATE(YEAR(B3),MONTH(B3),DAY(B3)+1)+"09:30",DATE(YEAR(B3),MONTH(B3),DAY(B3))+"09:30"))

here i have formula where date & time convert after (15:40- 23.59) to next day morning 09:30 am

e. =if B3 date and time is 08/01/2020 15:50 it will convert to 08/02/2020 09:30 am

simultinioulsy aft 00:00 to Morning 09:29 am it convert same date 09:30 am

eg.if B3 date and time is 08/02/2020 01:00 am it will convert to 08/02/2020 09:30 am

but as on on 2nd aug was sunday so i need to converet the same date AND TIME WITH 08/03/2020 09:30 AM

PLS HELP TO GET IT DONE

VINOD J

I need formula to calculate the aging for case created from the date of creation to the present date. which should be auto calculate on daily basis.

i have this formula =IF(O5"",O5-B5,Q2-B5) and when i drag it down it will be like this =IF(O6"",O6-B6,Q3-B6) how can keep the Q2 when dragging

Hello!

I think this instruction will help you solve the problem.

please i need your help

i have excell work sheet from 1st september to 30th with for formua , now i want creat new sheet for 1st october such that the 30th september minus (-) 1st octomber gives zero

Hello!

I’m sorry but your task is not entirely clear to me. For me to be able to help you better, please describe your task in more detail. Please specify what you were trying to find, what formula you used and what problem or error occurred. Perhaps you just need to subtract dates and subtract 1 more day?

Hi

I need to set a formula in excel so when one cell is updated with a month and year (e.g. Jan 20) the cell below it automatically updates to 3 months later (e.g. April 20). Does anyone know how to do this please?

Hello!

To add or subtract months from a date, use the EDATE function.

I hope it’ll be helpful.

I want to create cells that adds a range of days to dates with parameters: not to be less than the first day of the next month and not to exceed the last day of the next month. I enter a date in one cell, next cell adds 28 days but at least first day of next month, third cell adds 35 days not to exceed the last day of the next month.

Hello!

Formulas that calculate the first and last day of the next month

=EDATE(D3,1)-DAY(D3)+1

=EOMONTH(D3,1)

I hope it’ll be helpful.

Good day

I need a formula to help me with Invoicing payment dates. So if I receive an invoice on the 20th or before that, the invoice needs to be paid end of the following month (eg Invoice date - 20-Nov-20 and payment will then be 31-Dec-20), but if an invoice is received after the 20th of a month, it needs to be paid 2 months later (eg Invoice date - 22-Nov-20 and payment will then be 31-Jan-21)

Thank you in advance

Hello!

If I got you right, the formula below will help you with your task:

=IF(DAY(D1)>20,EOMONTH(D1,2),EOMONTH(D1,1))

You can learn more about EOMONTH function in Excel in this article on our blog

Thank you, Thank you, Thank you ... that is exactly what I wanted it to do

Hi, I need to define/include any real month, as to the ( from last day of previous month to one day before the last date of current month). Can i see a calculation for this?

Ex: Real month - (November 1st to 30th November)

My definition November - (31st October to 29th November)

Wayne C

I want to update 1 tracker, how can come todays date, if i'll open file before 12.00 AM, if any reason, i'll not able to open file before 12.00 AM, I want to previous date.

Buna ziua

Tot incerc sa aranjez un tabel in ordinea cronologica a datei calendaristice si nu reusesc.

Cum pot sa rezolv aceasta problema?

Am incercat multe variante si degeaba.

Cu multumiri

Hello!

You can learn more about sorting dates in Excel in this article on our blog.

I hope it’ll be helpful.

Hello

Sir,

Please tell us formula date to 09/04/2020 dd/mm/yyyy

Hello!

Please check out this article to learn how to change Excel date format and create custom formatting

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

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.