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) + 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:

### 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

Dear Sir below the excel format we have a do the calculate pending days with current days pls suggest

Date Customer Name Pending Balance Pending days

24-05-2022 RAJA ENTERPRISES 14231 ?

Hi! I can't guess how you want to calculate pending days from the data you provided. Describe in detail what you want to calculate, give an example of the expected result.

Hi,

Do you know how to make and divide this wording 01-Oct-2024 until 31-Mar-2025 into Start Date and End Date?

Thanks

Fetty

Hello Fetty!

Use substring functions to extract the text before and after the word “until”. Look for the example formulas here: Excel substring: how to extract text from cell. Then convert this text to a date using the DATEVALUE function or other methods described here: How to convert text to date in Excel.

=DATEVALUE(LEFT(A1,SEARCH("until",A1)-1))

=DATEVALUE(MID(A1,SEARCH("until",A1)+5,20))

Hi I need help please I have a sheet which I have registration numbers down and then I have made individual month columns going accross the top is there a formula that can highlight 6 monthly due from date of last inspection

Hello Carlie!

If I understand your task correctly, try to use the recommendations described in this article: Excel conditional formatting for dates & time: formulas and rules.

Excel formula help please! I'm not sure how to do this.

I need to find the total dollar amount spent-to-date based on a start date and the current date. So ideally, the dollar amount will update each day.

The dollar amount is a weekly rate (so should be divided by 7 for a daily rate).

How would I do this?

So Cell 1 is a weekly $ amount. Cell 2 is the "start date" and Cell 3 is the current date. Cell 4 would show the total dollar amount spent for the number of days between start and current.

Hello Sarah!

Find the difference in days between start date and current date. Use this article to do this: Calculate number of days between two dates in Excel.

If I understand your task correctly, the formula might look something like this:

=(A3-A2)*A1/7

Hello,

Unskilled user needing a little help please?

I currently use this formula to show me a date which an action has to take place by prior to an event date:

=WORKDAY(B4,-3)

Is it possible to create a fornula for the same result cell to return a text value of ‘Expired’ if the date returned is older than todays date, rather than show the date once the threshold is reached?

Hi! If I understand the question correctly, you can get today's date using the TODAY function and use it in the IF function. Based on this information, the formula could be as follows:

=IF(WORKDAY(B4,-3)<TODAY(),"Expired","")

Hello

Thank you. This solves part of my task. The reslts display '"Expired" in the appropriate date conditions, but if the date has not expired it returns a blank cell.

In the NOT expired result, I would like this to show the date which the intial formula =WORKDAY(B4,-3) shows..

Example of desired result: Today is 19/04/2023

Date of event Cell result

19/04/2024 Expired

20/04/2024 Expired

21/04/2024 Expired

22//04/2024 Expired (Allows for weekend as none countable days))

23/04/2024 Expired

24/04/2024 Expired

25/04/2024 22//04/2024

26/04/2024 23//04/2024

etc

Many thanks.

Hi! I believe that you can replace the empty string in the FALSE argument in the IF formula with the formula that you want to use. Have a look at the link I gave you.

How can i set a formula to calculate a date for the first monday 1 year after a set date.

i.e. if the set date is 11/4/24 the first monday 1 year after that date would be 15/04/2025

Hi! Add 1 year to the date as recommended in these instructions: How to add and subtract dates in Excel. Use the WEEKDAY function to set the weekday.

=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) + 7 - WEEKDAY(DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)),12)

Help please!

How to do the formula, let say:

<15 working days = 10 marks

31 working days = 0 marks

22/1/2024 - 31/1/2024 = 8 days

How to do the formula to in the marks?

Hi! Sorry, it's not quite clear what you are trying to achieve. Notes on how the date difference should be counted correctly: Calculate number of days between two dates in Excel. If this is not what you wanted, please describe the problem in more detail.

Help please! How can I create a formula for overdue work. Example: work was due on 3/5/204. It is now overdue, so I want today's date 3/15/2024. Other students are on time, so their work is due on 3/17/24

Hi! From your description, it is difficult to have a full understanding of your task. I’ll try to guess and offer you the following formula:

=IF(A1>TODAY(),A1,TODAY())

Read more: Excel IF between two numbers or dates.

Saloon Cars AD 26077 24/01/2024

Saloon Cars AD 35139

Saloon Cars AD 39761

Saloon Cars AD 39953

Saloon Cars AD 54024

Saloon Cars AD 10992

Saloon Cars AD 12056

Saloon Cars AD 13890

Saloon Cars AD 37692

Saloon Cars AD 43129

Saloon Cars AD 34405

In abstract i need, Saloon cars showing the count of dates as 1.

In short i want to count the dates with respect to the data i.e. Saloon cars

Hi! Sorry, I have no idea exactly what the task is.

It's hard to tell exactly what you're asking for as it's currently written. To count values by condition, you can use this guide: COUNTIF function in Excel - count if not blank, greater than, duplicate or unique.

For multiple conditions, you can use the COUNTIFS function :

=COUNTIFS(A1:A20,"Saloon Cars",C1:C20,"<>"&"")

If this does not help, explain what result you want to achieve.

May I ask a formula sample:

A1= 10 May 2023

A2 = 30 September 2024

What formula I can put so I can get the middle of the date? I've tried =IF(M2>16,TEXT(I2+(H2-I2)/2,"M/D/YYYY"),I2)

but it shows not correct format of the date like (7/1/2023)

Hi! Your formula does not use the values in A1 and A2. Therefore, I can't understand what you want to do. I'll take a guess and suggest this article: How to calculate average in Excel: formula examples.

Hello

How do I find if two dates have 12 months or less between them, if i have ID number and a date and the same ID number but different dates (sometimes 5 dates or more) not in the same row but somewhere else on the sheet

Hi! I don't really understand between which two dates you want to find the difference if you have 5 or more dates. I recommend reading this guide: Excel DATEDIF to calculate date difference in days, weeks, months or years. Or explain the problem in detail.

Hello Sir, I need to put conditional formatting on the date column to highlight everything that falls within 3 days from today. For example, today is the 15th, it should highlight cells that show 15th, 16, and 17 Dec. Anything before the 15th Dec or after the 18th Dec should not be highlighted. Please can you suggest?

Hi! You can find the answer to your question in this article: Excel conditional formatting for dates & time: formulas and rules.

Thank you Sir!

=IF(MONTH(D3-WEEKDAY((D3),2)+1)<MONTH(D3)*(D3-28-DAY(D3)+7)-WEEKDAY((D3-DAY(D3)+7),2)+1*(D3-DAY(D3)+7)-WEEKDAY((D3-DAY(D3)+7),2)+ 1)

this formula not working i don't know why.

I can't understand and correct your formula, as I don't have your data and can't guess what result this formula should return. The [value_if_true] and [value_if_false] arguments is missing in the IF formula.

Hello I need formula where i can check and balance for Active employee and leave employee month wise.

if employee is active then his salary continue till end of year and if employee left the company between the year then his salary will be shown till his last month.

Name Salary Joining Month Leave Month Status January February March April May June July August

ABC 5,000 January Active 5,000.00 5,000.00 5,000.00 5,000.00 5,000.00 5,000.00 5,000.00 5,000.00

EFG 2,500 January June Leave 2,500.00 2,500.00 2,500.00 2,500.00 2,500.00 2,500.00

HIJ 3,500 March Active 3,500.00 3,500.00 3,500.00 3,500.00 3,500.00 3,500.00

KLM 4,500 April Aug Leave 4,500.00 4,500.00 4,500.00 4,500.00 4,500.00

Total 7,500.00 7,500.00 11,000.00 15,500.00 15,500.00 15,500.00 13,000.00 13,000.00

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

=IF($G$2="Active",$B$2,"")

Thank you Sir,

for your quick response.

i know about this formula, but what i need to do, i need to make employee salary list month wise for a year,

in this list i will mention Employee Joining Month & End Month, and status will be Active & Left. if the employee end month is empty then status will be active and once we mentioned end month then status will change to Left.

if 1 employee join us on March he leave the company in September, then in this sheet his salary will be shown in March Column till September Column and remaining month will be empty.

i hope now i make more clear to understand.

Appreciate if i get the solution.

Hi! If I understand your task correctly, find the month number using the MONTH function and try this IF AND formula:

=IF(AND(MONTH(1&$C2)<=MONTH(1&F$1), MONTH(1&$D2)>=MONTH(1&F$1)), $B$2,"")

Hope this is what you need.

Thank you sir, you Formula is worked,

but there is some error.

if i select Joining date January and leave date march it calculate perfectly but if i select any month after march nothing is showing,

and once i select the Joining date April and Leave date Dec it will show all correctly.

so there is something missing in formula,

2nd if there is no leave date then salary will be shown in month but once i select the leave date then salary will be stop on that month.

if you can check the problem.

Hi! The formula I sent to you was created based on the description you provided in your first request. I have not been able to reproduce your problem. Note how the month names are written down. The formula works with month names as text, as you wrote in your question.

Dear Sir

Greetings to you

Kindly could you please guide, how can I link multiple excel sheet with IF formula,

For Ex: I have 5 excel columns with different dates, and I want to change the If formulated column by updating dates.

Example:, Chat 1 . received on 1 dec. > start count days, in next colum1

received response . after days > need to change count days 0 in colum1 and start new count days, in colum2 and so on.

Regards

Hi! Your task is not completely clear to me. To understand what you want to do, give an example of the source data and the expected result. In which column do you enter the date 1 dec., and what results do you want in the other columns.

When I deduct 2 dates from one another that are exactly 24 hours apart, the answer is returned as zero, not as 24 hours

Hi! Try to follow the recommendations from this article: How to show over 24 hours, 60 minutes, 60 seconds in Excel.

Hello, please create a formula. I need to get the number of days overdue from the requested delivery date which automatically updates everyday.

Ex. Today is October 23rd, customer's requested delivery date is on the 20th. So the answer must be 3 days.

Hi! Try to follow the recommendations from this article: Calculate number of days between two dates in Excel.

Date CELL Repeated Count Ticket#

02-Sep-23 3000092822 2 Ticket#102922098301

07-Sep-23 3000092822 2 Ticket#102698554587

ho can I find difference of dates in days from date column in an other column as I have same values in cell column

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

=INDEX(A2:A10,MATCH(B1,B2:B10,0))-A1

Find the date in column A that has the same value in column B using INDEX MATCH function. Then find the date difference.

Dear Sir

Once again thank you so much.

My meaning is how to supply the start date directly to the the formula, use the DATE function

=IF(AND(B2=Sep/23 "Pass", C2=Oct/23"pass"), "Good!", "Bad")

Submission date Sep/2023- Receiving comments Oct/2023- Last Rev. No. Good

Thank you

Please re-check the article above since it covers your task.