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 syntax

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

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.

The DATE function is available in all versions of Excel 2013, Excel 2010, Excel 2007, 2003, XP, 2000 as well as in Excel 2016.

Excel DATE formula examples

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

Example 1. A simple DATE formula to return a serial number for a date

This is the most obvious use of the DATE function in Excel:

=DATE(2015, 5, 20) - returns a serial number corresponding to 20-May-2015.

Instead of specifying the values representing the year, month and day directly in a date formula, you can get some or all arguments as results of other Excel date functions:

=DATE(YEAR(TODAY()), 1, 1) - returns the serial number for the first day of the current year.

=DATE(YEAR(TODAY()), MONTH(TODAY(), 1) - returns the serial number for the first day of the current month in the current year.
Using the DATE function in Excel to get a serial number representing a date

Example 2. Excel DATE formula 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:

=DATE(A2, A3, A4) - returns the serial number for the date, taking the values in cells A2, A3 and A4 as the year, month and day arguments, respectively.

Excel DATE formula to return a date based on values in other cells

Example 3. DATE formula to convert a text string 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 text string into a serial number representing the date:

=DATE(RIGHT(A2,4), MID(A2,3,2), LEFT(A2,2))
DATE formula to convert a text 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) + 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:


    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 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 dates tutorial:

436 Responses to "Using DATE function in Excel - formula examples to calculate dates"

  1. Shanu Singh says:

    Hi Daer All,

    Pls someone help in my problem

    I have a problem for calculating salary between two dates e,g Logic 1 Manual calculation & logic 2 is the based on the two dates. When calculated from logic 2 there is different between logic 1 amount 1,797. The case listed.

    Logic 1
    Manual Days calculation
    Start date End Date Salary Montly days Formula Prorata salary
    24-Mar-17 31-Mar-17 49,500 8 =E9/31*F9 12774
    Manual 12 Months calculation
    Start date End Date Salary Montly Months Formula Prorata salary
    1-Apr-17 31-Mar-18 49,500 12 =E14*F14 594000

    Total days+Montsh 606774

    Logic 2
    Start date End Date Months Salary Montly Formula Prorata salary
    24-Mar-17 31-Mar-18 12.22 49,500 =F2*E2 604,977
    Total 604,977
    Different Logic 1 Vs Logic 2 1,797

    Months calculation formula

    Note : Logic 1 is giving correct amount.

  2. Kristine says:

    Hi there,

    I am using a Gantt Chart I made years ago and can't figure out why I created the formula the way I did.


    J5 = is the Start Date (which is 1st of June, 2017)
    M8 = 1 (Day of the week which was Sunday)

    and then I have M1 whose value is 0 <-- this is the part that I needed to remember/understand, why I declared the value as 0

    Given that the Start Date is 1st of June, the actual value I get is the 28th of May

  3. adrian says:

    Hi there

    Wondering if you could help me with this!?
    Having a xcel file and im trying to get xcel to give me the proper dates depending on a bar code and the no. of days which the product has! So lets say i have:
    C4(bar code), H4(life shelf 12 days),I4(day which should be)

  4. Jessie Perez says:

    Hello, i have a different value on Column A, and i have different date on column B. And I want to know the beginning & ending date for that particular value. What is formula for that? Thanks

  5. Pia says:

    Hey!!i am working on an hotel management excel where i am unable to calculate or either put date meaning i am not able to adjust check in time and check out time..Pls Help

  6. Juan says:

    Suppose I want to have a column that has dates of completion of training and I want to have function where there are three colors, green is considered current, yellow is 60 days coming up on expiration and red has expired. How can I create this? Is it even possible? Thank you.

  7. Sean Green says:

    Can I update the cell references in a formula (i.e. add the next months value into the formula) by changing the month in a drop down list? I am looking to extend the formula each month and would like to accomplish this without manually changing every line.

  8. Muhammad says:


    I need an urgent help. I am maintaining a sheet where Supplier ID and Term start date is being recorded. Each supplier is appearing multiple times with a different term start date. I want to automatically put the respective term expiry date which will be a day before next term start date. Please help me with this.


  9. pavan says:

    hello, what the formula of show month period like 01 January 2017 to 31 January 2017

  10. Barry Hallman says:

    I have column D for the date an item is issued. column E will calculate the date from column D plus 180 days. I would like to be able to input the current date into column F. And have it calculate into column G the amount of days between the current date in column G and the due date in column E. If you can help me with formulas I would be greatful

  11. Jenny Loan says:

    ty so much for your amazing page and information.

    I am trying to create a spreadsheet for my sales tracking. If I ordered a specific document on xx/xx/xx date and have input that date into Column A2 for example(labled -date requested), I will input the received doc. date in column B2.
    But if no document is received. I would like for column B2 to turn green(when column A2 date is input(day1) and yellow when 1 day past the initial A2 date, and red when 2 days or more past the initial A2 date(request date) to inform us that the document has not been received. However, if a date has been inputted into B2 these rules(Red, yellow and green) will stop, because the date was added to B2 therefore implying that the document was received.

  12. aditya mittal says:

    Hi Team,

    Your above methods are very helpful.
    I wanted to know how i can get the date of next month.
    For eg-: if i input 23-01-2017 i should get the output 22-02-2017

    • Mary Trifuntova ( Team) says:


      Thank you for your question.

      Enter your date in any cell (A1 in my example) and try the following formula:


      As the logic of the DATE function is the following: DATE(year, month, day), the formula above adds a month to the date in A1. You can change the number of days/mounts/years as needed.

      Please let us know if you have any other questions.

  13. Tanvir says:

    Journey start (12/5/2017. 6:00 AM )
    return time (16/5/2017. 9:00 AM )

    Return have total dinner, lunch, breakfast, and accommodation quantity.

    after 7:00PM he will not get dinner.
    after 1:00AM he will not get accommodation.
    after 8:00AM he will not get breakfast.
    after 12:00pm he will not get lunch

  14. Pandiyan says:

    I need some help data 1 is same name but different output date.but data 2 is Unique name,so i need for which date in higher date only we have represent the output date.

    Data 1
    hai 4/20/2016
    hai 4/22/2016
    hai 4/21/2016

    Data 2 output
    hai 4/22/2016

    • Gabar says:

      Change date Format
      20 April 2019
      22 April 2019
      21 April 2019
      22 April 2019

      And use this formula =LARGE(B5:B8,1)
      B5:B8 - Select Cell range area

  15. Pandiyan says:

    I need some help data 1 is same name but different output date.but data 2 is Unique name,so i need for which date in higher date only we have represent the output date.

    Data 1
    hai 4/20/2016
    hai 4/22/2016
    hai 4/21/2016

    Data 2 output
    hai 4/22/2016

  16. sara says:

    I need to use formula to calculate expiry date from age using following condition. If age is less than 40yrs, expiry date should be 5 years from examination date. If age is equal to or more than 40yrs, expiry date should be 2 years from examination date. Please help. "D" TO BE DONE USING FORMULA.

    A B C D
    1 Age Examination Date Expiry Date
    2 57 7/25/2017 7/24/2019
    3 39 7/25/2017 7/24/2022
    4 40 7/25/2017 7/24/2019

  17. Fonzie says:

    I have hundreds of items with different project periods. Some have reports that have to be done monthly, quarterly, semiannually and annually. We roll the log of these items each month to track those that are currently due in a particular month. Does a formula exist that can calculate these due dates automatically??

    Today's Date 9/20/2017
    Month Ending 9/30/2017
    Project Period Report Due
    Project A 10/1/2016-9/30/2020 Q ?
    Project B 4/1/2017-3/31/2019 A ?
    Project C 9/30/2016-9/29/2018 SA ?
    Project D 7/1/2015-6/30/2018 M ?

  18. Rami says:

    Hi guys,

    Here's what i'm trying to get.

    I have a list of students with date of births, the rule to enter sports tournaments are the following

    U11 (Year 6/ Grade 5)– 1st Sept 2006
    U10 (Year 5/ Grade 4)– 1st Sept 2007
    U9 (Year 4/ Grade 3)– 1st Sept 2008
    U8 (Year 3/ Grade 2)– 1st Sept 2009
    U7 (Year 2/ Grade 1)– 1st Sept 2010
    U6 (Year 1/ KG)– 1st Sept 2011

    The date of births are set up this way 21-Dec-2008 (8.9) and i would like to have a formula which will indicate if a students is allegeable to play for the U11 or U10 ....

    thank you in advance

  19. Wendell says:


    I am working on next year's budget: Let's say an employees hire date is June 1, 2017 and they will be getting anywhere from a 0% - 3% merit increase on June 1, 2018.

    1 - I need to calculate the current salary amount from January 1, 2018 to June 1, 2018 at the current hourly rate ($10) by the number of hours worked per week (20) from January 1 - June 1.
    2 - I need to calculate the merit increase (3%) from June 1, 2018 to December 31, 2018 to get the new salary amount for that period.
    3 - I need to know what my total budget would be for the year and is it possible to get the total dollar amount in one cell?
    4 - Is it possible to create a formula to auto update this each year without having to manually change the year?

  20. Camilo says:


    Let's say that I have columns A and B. I need to insert a date in column A and automatically appears a date but 90 days later in column B. How can I do this?

    Thank you.

    • Gabar says:

      Hello Camilo
      use this Formula your worksheet.
      =TEXT(SUM(A1+60),"DD-MMM-YY HH:MM")
      A1- Columns A
      Formula use - Columns B
      i think that we works.

  21. Kyler says:

    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.


  22. SAJEER says:

    >30 days from today column must show "0". how to make formula.

  23. Spandana says:

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

  24. Dale says:

    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?

  25. Spencer says:

    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".

  26. Arivananthan says:

    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


  27. Prabaa says:


    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

    • 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!

  28. Gil says:

    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!

    • joe says:

      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...?

  29. Joe says:

    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!

  30. Prabakaran says:

    Hi the below may be helpful to you.


    Replace E8 with your active cell reference

  31. Nonya says:

    I have a question.

    I have a formula that pulls a date from another worksheet.
    In the cell adjacent to that "second date formula" I have a formula that creates the next month.

    Below each date I have a formula like this.
    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?


  32. Tina says:

    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?

  33. Soujanya says:

    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?

    Sl. Employee Years Range
    1 Abc 5 5 - 6
    2 DEF 8 8 - 9
    3 GHI 5 5 - 6
    4 JKL 5 5 - 6

  34. Jasmine says:

    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?

  35. hayat khan says:

    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 ?

    • Gabar says:

      Hello Hayat Khan
      use this formula i will make sure there formula is work.
      D14- mention Year Cell Range
      C14- mention Date Cell range

  36. Rohit says:

    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

  37. signals says:

    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

  38. Lynn Keeney says:

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

  39. gary b says:

    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.

  40. Paula says:

    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.

  41. Doug says:

    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.

  42. krishna prasad R says:

    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

  43. shady says:

    I have a difficult question.
    I have a duplicated codes with different receipts dates.
    How can I get the latest date for each code

  44. krishna prasad R says:

    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

  45. SHAMS says:



    CY 01 (01.01.2016) THE RESULT SHOULD COME O.K.


    CY 03 (05.07.2009) FOR HPT


    • Doug says:

      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:

  46. Cindy says:

    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?

  47. Kari says:

    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?

  48. Harsh Khimasiya says:

    Want to formula
    0-30 days 2%
    30-60 days 1 % and
    60-90 days 0 %

  49. Lakshmi says:

    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

  50. gangadhar reddy says:

    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.

  51. Raj says:

    Hello There,

    You can use below formula:-
    DATE(YEAR(A1),MONTH(A1),DAY(A1)+30) for 30 days.

  52. Steve says:

    How to get the formula of 30 DAYS OVERDUE if the given is date today, terms and due date ?

  53. Suthan says:

    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.

    • Doug says:

      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.

  54. dara says:

    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

  55. Lijo says:

    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

  56. F&A says:

    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?

  57. Pratik says:

    Hello sir,sir which formula used for all company employee experience in age between 0to5,5to10,20to30 in excel

  58. Gordon says:


    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


  59. arman says:

    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

  60. JD says:


    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.

  61. Fouad Amhaz says:

    hi all,

    in my work an extract old data from our system.
    but we face that the date is mentioned as day/month/year.
    so when i open this extracted file in office 365, excel read these cell as text and not as date number.
    so here i need a formula that convert these cells as month/day/year in order to can read it as date value in 365 excel.
    i.e: 20/4/2018 old version it means 20 April 2018.
    it must be 4/20/2018 for the new version.

  62. kapil says:

    HI SIR,



  63. komal says:

    Hi All,

    I am facing one issue while calculating date from one cell to another

    I have cell B4 and date in that cell is 31/10/2018 06:29
    I am using date formula in cell D like =DATE(YEAR(B4),MONTH(B4),DAY(B4))

    but i am getting error.

    Please help me.

    Thanks in advance for help

  64. komal says:

    Hi All,

    I am facing one issue while calculating date from one cell to another

    I have cell B4 and date in that cell is 31/10/2018 06:29
    I am using date formula in cell D like =DATE(YEAR(B4),MONTH(B4),DAY(B4))

    but i am getting error.

    Please help me.

    Thanks in advance for help

  65. Brent says:

    Hi all,

    I need a formula that gives me a "Y" or a "N" on whether a date is greater than 180 days.

    Thanks in advance!

  66. PrevailerHearts says:

    Kindly assist me. I have dates in MMM YYYY format, that is Jan 2005, Aug 2005, Sep 2014, Jan 2015, Aug 2004, Aug 2014 not following consecutively in a column. How can I find out if an entry is skipped using an excel function?
    Please help.

  67. RAJKUMAR says:

    today date () is constatnt 02/01/2019 cell no u1

    16/10/2019 is cell no u25

    21/10/2020 is cell no u27

    16/01/2020 is cell no R25

    our formula is IF((MONTH(U25)&YEAR(U25)<MONTH($U$1)&YEAR($U$1)),R25,"")

    actual result is empty

    BUT RESULT SHOWS 16/01/2020,

    so kindly help me

  68. Obainor Akande Dele says:

    please help me out with excel formula that will generate serial number from household number of 7
    7 - 1
    7 - 2
    7 - 3
    7 - 4
    7 - 5
    7 - 6 and
    7 - 7
    How can i get the formula that will do the serial numbering from 1-7 as stated above

  69. Jenny OConnor says:


    I have a date in column H9 (formatted as mm/dd/yyy) I also have a date in column K9 (formatted mm/dd/yyyy) secondary format is H9+7 to return a full week.

    I then have column A22 - A28 that I would like to return the "MONTH" from Column H9 and then in column B22 - B28 to return the days 1-7 from H9.

    Any ideas?

    Thanks - Jenny

  70. Azmi says:

    Hi all,
    Can someone help me on how to add a month in advance in a cell when select a month from a drop down from different cell

  71. Babunoufal says:

    I am Add sheet A1 year B1 Month then auto update D1 first Friday date, E1 second Friday Date, F1 3rd Friday, G1 4th Friday H1 5th Friday (january not 5th Friday then H1 cell show blank or Nill.

    A B C D E F G H
    2019 01 or Auto update Auto Update Auto Update Auto Update Auto Update
    January (04/01/2019) (11/01/2019) (18/01/2019) (25/01/2019) No date then Nil Or Blank

  72. Jan says:

    I have inherited a file with a pivot table showing years 2016, 2017, 2018 and >12/31/2018. How do you change the >12/31/2018 to show as 2019 so that I can pick January and February?

  73. Mayur says:

    Hi, I want to calculate the following logic built into excel for calculation EMI for loan
    - A person can select one of the two EMI starting dates (either 10th or 15th)
    - Say If I disburse USD 100000 on 15th Feb 2019, and the borrower selects 10th as the EMI due date, the first EMI will be due on 10th April 2019 or if say he selects 15th as the EMI due date, then the first EMI will be due on 15th April 2019. The difference between 15th Feb 2019 to either 10Mar2019 or 15th March 2019 will be the pre EMI date and interest will be calculated accordingly.

    Can you help me build this logic in excel so that If I select the disbursement date and 10th or 15th as the EMI due date, the EMI due date gets calculated automatically

  74. chakri says:

    =IF(B10="Today",(Today()),IF(B10="Yesterday",Today()-1)) I need a result while i select the dropdown text like "Today" to day date will be display on cell.please give a solution

  75. Bhargavi says:

    Hi ,
    In excel for employee details i need to validate the date,so that when the user is inputing any date it should in date format and no other format(string ,text or anything),If there is wrong date entered it should not be taken can you please help me on this.

    Bhargavi M

  76. thomas says:

    I am involved in sports analytics. I am attempting to create a formula that searches back a certain number of days and sums the total within that number of days to come up with a stat similar to "goals scored in the last 5 games" or "Goals scored in the last 20 days"
    something along those lines. I have been able to figure out how to search this with a static formuala (search back to a certain date). Have not been able to figure out how to have it search from TODAYS date and back 15 days. Any help would be appreciated.

  77. Tikam Chand says:

    06-04-1998 convert in text Six April Nineteen Hundred Ninety Eight
    kindly help me

  78. Shika RM says:

    Hello, I need some help here
    I have lots of sales data that I have to sort like this - Earlier this quarterly, Last week, This week, Later month, later this quarter, etc. All dates are in mm/dd/yyyyso i would like define them with text like This week, Later month and so on.
    There are booking dates and actual dates - So another condition to be added if there is an actual date then use actual date.

    Is there any logical formula i can use?

  79. shakila says:

    how to calculate overdue amount with compare between two dates same. example. today 05.04.19.if due date same that then overdue amount current due otherwise current due plus other emi.

  80. Glenn says:

    Please help me, i need a formula that compute number of days of a certain years
    that in first 2 years is equivalent to 20 days deduction
    3 to 5 years is equivalent to 23 days deduction
    6 to 10 years is equivalent to 25 days deduction
    11 and above is equivalent to 30 days deduction
    Start date: March 23, 2017 end date: April 10, 2019 = 2 years 0 months 17 days total of 480 days

    *** Hoping for you support and Help.. God Bless

  81. mayank says:

    I have 3 date one is TDR Raised then TDR Closed Target Date & Last TDR CLOSED DATE.
    I want to calculate Lead time between TDR Closed Target date with the current date tha I have done, but after that I need to stop calculating the lead time after the TDR Closed Date.
    Please suggest

  82. Venugopal says:

    Can u pl help me how to remove slashes by using formula. Ex: 12/31/2018 (MM/DD/YYYY) to 12312018.
    Thank u in advance.

  83. Ashish Sharma says:

    Can you help for below query
    Payment Date DUE DATE
    03-04-2018 07-05-2018
    02-05-2018 07-06-2018
    22-06-2018 07-07-2018
    25-07-2018 07-08-2018
    I want to get due date by formula. Can you tell me by which formula I can get the due
    date as per above.
    Ashish Sharma

  84. Miki says:

    I would like to do a report to check if the customer paid on time:
    1. This is acceptable:
    (a) Due Date Due Date Due Date > 7 days

  85. lazarus mabipela says:

    Please help me , i need a formula in my report to do it fast. The report is like this:
    i have to calculate days taken to pay the customers, but holidays and weekends are not included. These days are calculated from the day invoice is received untill it is paid.

  86. A Kumar says:

    I am Calculating the Year and Month completed between two date
    Start Date = 06-Jul-2009 and End Date = 31-Mar-2019

    Difference of Both Date is 9 Year & 8 Months, But i want a formula to calculate easily

  87. amanda says:

    I need to convert 13Y 11M into a date using today as the reference.

  88. Amit says:

    Please refer to my problem as i want to a calculation sheet like 31/03/2014 to 15/05/2014 then the result come Mar 01 days April 30 days and May 15 days. please reply

  89. Robert Graham says:

    Not seeing it in this thread so I will ask here.

    We have an event every third sunday of each month. Need a formula that can depict that and return a date.

  90. Ram says:

    Dear Team,

    I need formula where supposed I have dates from 1st May to 3oth May there have 100 counts.
    Now I need formula where from date 1st May till 15th May= month should be May only and there days which is more then 16th till 30th May its come in next month which is june

    Please help me

  91. Amitava says:

    Please help me with the excel formula. I am intending to mark first and second in a cell based on series of date of birth available in a different column by using a cut off date.


    Dear , I seek your help to change the date 12/01/2019 to 12012019 format please..
    it would be great help

    • Hi Mohammed,

      The easiest way is to apply a custom date format to the cell. Here's how:

      1. Select the cell(s) you want to format.
      2. Press Ctrl+1 to open the Format Cells dialog.
      3. On the Number tab, select Custom from the Category list and type one of the below format codes in the Type box.
      4. Click OK to save the changes.

      If 12 is the month and 1 is the day, use this format: mmddyyyy
      If 12 is the day and 1 is the month, use this one: ddmmyyyy

      For more information, please see How to create a custom date format in Excel

  93. Jason says:

    I have a column titled 'Review month' and a column titled 'Requires Review', the 'Review month' column is just text (e.g May). I am trying to create a formula so that if 'Requires Review' is within a month of 'Review month' it automatically fills the cell Red and says yes. if it is not within a month then it is to fill the cell Green and say no.
    Is it possible to create a formula for this or conditionally format the cell to do this?
    Any further information needed then please ask.

  94. Kelly says:

    I wonder if anyone can help?
    I need to work out the date that a valuation total went over 90% of the total Contract value?
    i.e. column A would be the valuation date and column Z is a formula with the total contract % so far, I need to work out the date when if first goes over, any way of doing this


  95. Timothy Otzenberger says:

    If I use the edate formula for 06/30/2019, and then want to go back 1 month it returns a value of 5/30/2019, however I there are 31 days in May, I need the formula to figure out what month I am subtracting and caculate it correctly so that I end up with the actual end of the month day of 5/31/2019.
    =EDATE(E97,B97) where E97 is 06/30/2019 and B97 is -1

  96. Anand Shah says:

    I want to use formula for date sorting
    example: Item A have a 3 delivery date like Row 1 : 10/02/2019 Row 2: 18/03/2019 Row 3: 05/01/2019 Row 4: 20/05/2019
    Than date serial number require using formula like:
    Row 3: Serial number 01
    Row 1 Serial number 02
    Row 2 Serial number 03
    Row 4 Serial number 04

    Please suggest which formula use for the same.

  97. ELLE says:

    HELP! I want to know a formula,
    Lets say if the person join the company before year 1993, he will retire at year 55years old AND IF the person join the company in 1993 and above he will retire at year 60years old.
    D12 = date of birth
    G12 = date joining the company
    and if its a woman, she will retire at 60years old no matter what

  98. Innocent says:

    pls, I want to do conditional formatting whereby the cell is highlighted yellow from 10 days before maturity till the day its matures. on the day of maturity, I want the cell to turn green.

  99. sara says:

    how could I get remaining months? (2 years (Length of Lease) minus a date (start of Lease))?

  100. Stickleback says:

    Need some help. I created some sort of Date Picker from 3 cells (A1- dates 1-31, A2- months 1-12, and A3- years) dropdowns. Using Formula in a different cell =IFERROR(DATE(A1,A2,A3);"Not a proper date")
    Formula works as such, but if I pick from dropdowns 30.feb.2019, the formula returns 01.mar.2019...
    How can I fix this so i get in return that the date is not correct?

Post a comment

Unfortunately, due to the volume of comments received we cannot guarantee that we will be able to give you a timely response. When posting a question, please be very clear and concise. We thank you for understanding!
60+ professional tools for Excel
60+ professional tools for Excel
2019–2007 to do your daily work
Incredible product, even better tech support…AbleBits totally delivers!
Deborah Bryant
Anyone who works with Excel is sure to find their work made easier
Jackie Lee
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
AbleBits suite has really helped me when I was in a crunch!
Nelda Fink
I have enjoyed every bit of it and time am using it
Christian Onyekachi Nwosu
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Sheila Blanchard