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 2019, Excel 2016, Excel 2013, Excel 2010, and lower.

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.

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:


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 Date format to the formula cell.

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

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

  1. Scott says:

    I've spent a week or so looking for a formula. I recently came across you site. It's really good explaining excel. Thx. Can you help with my issue:

    Column A is descending non consecutive dates.
    Column B is prices.
    In colum C I want to add all the prices of Column B if they are between two dates in Column A
    So basically all the $ spent in a weeks in column C

    I assume some combination of vlookup, datevalue, if.
    thank you

    • Hello Scott,

      Thank you for your feedback!

      You can try using the following formula for your task:

      Here B1:B7 is the range with the prices, A1:A7 is the range with dates, and the dates in quotes are the conditions for summing values in column B. Thus it sums values in column B if the dates in column A are between January, 2 and January, 9.

      • ANGEL says:

        HI IRINA,
        Why i try to use your formula above, but still cant work? actually i need do one formula, for calculate from 1st Nov to 30 November total quoted amount
        Please advise. thanks

  2. Kate says:

    I am trying to convert text, imported from a report in a another program to a date. The text reads "January 1 2015". It will only recognize it as a date with a comma. I do not want to go in by hand to add the comma by hand in 100s of cells.


    i want to insert a formula for converting date into day
    exa. 27/12/2015= sunday
    plz sugges

    • Hello Preetam,

      You can use the following formula to return the day of the week:


      Here A1 is the cell with the date.

      • INDRAJEET says:

        Date taken For calculation of Invoice submission ( MS Approved date or Hoto date which ever is later)
        INVOICE SUBMISSION DATE-01/06/2016

        MS APPROVED DATE-12/05/2016

        HOTO DATE-18/05/2016


      • uthpala says:

        Hello Irina,

        I want to increment the day without incrementing the month in cells along in a raw (in A1,B1,C1,D1,E1...)etc...


        Please suggest.

  4. HR says:

    How do you add years to a current date to find new date (i.e., 01/01/2015 + 15 = 01/01/2030

    • Hello,

      You can use the following formula to add years to the current date:

      You can also replace TODAY() with a reference to a cell with a date.

      • Milos Norge says:

        Hi Irina,

        I was looking for the same solution as HR, but I still cant get the result.
        Date format is 19.10.2016. in C3 cell, and I have to add 6 months so the formula should be:
        =DATE(YEAR(C3), MONTH(C3)+6, DAY(C3))
        but I get only pop up message that something is wrong.
        Can you please help me?

      • Corrie says:

        Thank you.. this worked perfectly! I calculated my client's 18th birthday with this and their DOB. =DATE(((1997)+18), 1, 15).

  5. Chintan Prajapati says:

    Request you to help in below date format...

    Sample : 24121550200128

    First 6 digit is date like 24.12.15.

    how can i bifurcate date 241215 to 24.12.2015 in one formula...

  6. billy says:

    How would I go about trying to use a formula to project the time and day that a material would be completed? My example would be If my ending total is 5000 and I knew that I go through 250 per day and today is Tuesday 8 pm. What would my formula be? Greatly appreciated!

    • Hello Billy,
      You can use the following formula:

      Here D6 is the cell with the start date, F6 is the cell with the ending total, and E6 is the cell with the value you'd accomplish every day.

      Please make sure you set the cell format to Date with time, e.g. 3/14/12 1:30 PM.

  7. mahmud says:

    Can i use a formula in excel to calculate year & month ? as example: an employee joining date is 1/23/2015 . so excel will show that today he complete his (1 year) of job in this company ..... is it possible?


  8. mahmud says:

    Hi Svetlana Cheusheva,
    I need one more help.. that is about auto increment calculation.. the situation is " when an employee completes his one year in a company, he will get 5% increment on his basic salary " so is it possible in excel to calculate this?
    if you send me your email id, i can send you an attached salary sheet that i preparing.


    • Mahmud,

      You can use a formula similar to this:

      =IF(DATEDIF(A1, TODAY(), "y")>=1, B1*1.05, B1)

      Where A1 is an employee joining date, and B1 is that employee basic salary.

      Please note, the formula increments the basic salary by 5% for all employees that have worked one or more complete years.

      • mahmud says:



        its working as you say one or more complete years but if here the increment add after every one year.that means after every one year the basic will increase 5%. is it possible?

        thanks for your continuous support.

  9. Mahesh says:

    in A1 I have a date in dd:mm:yy. In A2 i want to build formula which will accept only date greater than A1. If earlier date is entered, it should return "INVALID".
    Please help.

  10. Judge says:

    Great info that you have here. Please help. I am doing a scheduling order. I want excel to calculate a date for me that is the "first thursday closest to (but past) 60 days later. I already have the "today" date on the order. So I wanted the above to be at least 60 days away but it has to be a thursday. Then I will have two other cells that will take that date and add 14 days (pre-trial briefs date) and the other would add 28 days. (trial date). Thanks and keep up the great work. You are amazing!

  11. Andrea says:

    I'm looking for a formula to put "YES" in one cell when one of the dates from my holiday list falls between the payroll start and end date.

    I have holidays listed in cell A1 through A54

    I have the payroll start date in B2 and payroll end date in B3

    I'm trying to write a formula in C1 that says when any date from A1-A54 falls on or between B2 and B3 put "YES" in C1
    Any help ?????

  12. Lee says:

    I have set up a tracker for holidays taken and booked ,sick days and toil my problem is i need a formula that updates the holiday column when the date the holiday is booked for is reached eg 23 days per year,10 days booked for 3-13 march how can i get this to update automatically instead of me having to change it from booked to taken?

  13. Andrea says:

    One more if I may?

    Look in Column A (Row 1 thru 100) for a number less than 0
    If there is one, then look in Column B (Row 1 thru 100) to see if the date is between M1 and M2 - If so "yes", otherwise "no"...

    (I tried working with the formula you gave me for the holidays, but I'm obviously missing something because I can't get it...

    • Vijaykumar Shetye says:

      Use the formula

      The start date has been entered in E1, and end date in F1.
      If you want different start date and end date for each row, then you may use E1 instead of E$1 and F1 in place of F$1.

      The signs =E$1 and B1<=F$1 may be eliminated if required.

      Vijaykumar Shetye,
      Goa, India

  14. Mahesh says:

    Request help in resolving following:

    1. Financial year (FY) is April-March i.e., “1-Apr-any year” to “31-Mar-next year” e.g., 1-Apr-2015 to 31-Mar-2016.

    2. Cell A1 should accepts any date, month and year (DMY) e.g., 21-Apr-1999 or 2-Jan-2006.

    3. Cell B1 by default should return corresponding FY end DMY i.e., “31-Mar-corresponding FY” e.g., 31-Mar-2000 or 31-Mar-2006 in point 2.


    • Vijaykumar Shetye says:

      Use the formula

      Vijaykumar Shetye,
      Goa, India

      • Chanra says:

        Request help in resolving following:
        Please help me:
        1. I want get result date confirm probation staff 3month after start join to work.
        Ex: date join : 12-jan-2017 but i want to know witch date he completed probation 3month

  15. Pam K says:

    I have a cell with an expiration date for a contract. I need the row to change colors 60 days and another color 30 days, etc from the date. How would I create a conditional format for that one?

    • Vijaykumar Shetye says:

      Dear Pam K,

      Select the cell in which the expiration date is present, example B2.
      Go to Home - Conditional Formatting - New rule - Use a Formula to determine which cell to format - Format values where this formula is true
      Enter the below formula
      Go to format - Fill
      Select Orange colour
      Click OK twice

      Repeat the above with the below formula
      Fill Red colour.

      The rules are applied in the order shown. Hence the formula
      =IF(TODAY()>=A1+90,1,0) and format Fill Red colour should be above the other formula in the list. The order can be changes by using the arrows above the list.

      Change the cell references as required.

      Vijaykumar Shetye, Goa, India

      • Gerard says:

        I needed a formula that would calculate today's date against a Due Date and change the cell color to yellow if it is between 60 and 31 days of the due date, and red if it is 30 days or less of the due date. This is the formula I placed in the Conditional Formatting for the cells:

        (For yellow cells) =IF($E2>=TODAY()+31,$E2<TODAY()+60)

        Which basically says, "Fill cell with yellow if today is between 31 and 60 days from due date (cell E2)."

        (For red cells) =$E2<=TODAY()+30

        Which is, "Fill cell with red if cell E2 is 30 days or less."

        I hope this answers someone's question because I could not find an answer anywhere. I was forced to learn formulas. LOL

  16. rocky says:

    i have a target date as 12/23/2015, If this date is falls between 12/01/2015 & 12/31/2015, then the cell should be updated as '2', else cell should update as '0'

  17. PAIN says:


    my wish is how could I ad 24 hours to date (12/03/2016) and receive in the other cell the new date , it is about a delivery time important to a client . I have tried different formula but do not work properly.

    with the best regards


    • Vijaykumar Shetye says:

      Dear PAIN,
      When you add 24 hours to any date, it is equal to adding 1 to the date. In your case the result of 12/3/2016 + 1 should be =13/3/2016.

      Excel treats dates as numerical values, and merely displays the values in the format desired by us.
      The numerical value of one complete day 1 Jan 1900 is 1, since it the first day in the calendar which is used in Excel.
      The numerical value of the date 31 Jan 2016 is 42400, which means that it is the 42400th day after 1 Jan 1900.

      It seems to me that you have not expressed your query correctly.

      Vijaykumar Shetye

  18. Abdul Hameed says:

    Hello everyone!

    This might not relate to the topic but rather its a bit more advanced in nature. I'm working on a complex formula but I'm lagging cause of the dates.

    Let's assume that colum A contains different dates. Now, how do I get the date which is the latest of all but it should be less than the date I've picked.

    I hope my question is understood.

    • Vijaykumar Shetye says:

      Dear Abdul Hameed,
      Use the Array Formula

      Cell references are as follows.
      List of dates is in cells A1:A100,
      Reference date is in cell B1.
      Change the cell references as required.
      When entering an array formula, use Control+Shift+Enter, instead of Enter.

      Vijaykumar Shetye, Goa, India

  19. Soum says:

    How can I use excel function to find age in dmy by subtracting his date of retirement from his date of birth

    • Vijaykumar Shetye says:

      Dear Soum,
      Your question is not clear. I have not understood why you want to find a persons age by using his retirement age.
      By subtracting a persons date of retirement from his date of birth, you will get a negative value.
      If you subtract the date of birth from his date of retirement you will always get the number of days equal to 21915 for a retirement age of 60 years.

      You can use the below formula
      This will subtract the number of years from birth (cell A1) to retirement (cell B1). But again the answer will always by 60 or whatever is the retirement age.

      If you want to find age, use the formula
      Format the cell as Custom Format Type dd mm yy.
      I am surprised by the requirement of this specific format. Eliminate the spaces between dd mm and yy if required.

      Vijaykumar Shetye, Goa, India

  20. Mindy says:

    Can you explain what the denominator of this formula is calculating?

    This came from a spread sheet I am working with that is labeled change/day.

    • Vijaykumar Shetye says:

      Below is the explanation of the denominator.

      The DATEDIF function returns the difference between two dates.
      The unit can be specified as days, months or years.
      (For detailed information, refer
      The end date must always be greater than the start date, otherwise the Excel DATEDIF function returns the #NUM! error.

      In your formula, the START DATE is the sum of the following 3 parts.
      1. DATE(1899,12,31),
      2. 0*7 and
      3. IF(B2>60,B2-1,B2)
      Let us understand each part separately.

      The DATE function returns the sequential serial number that represents a particular date, when the year, month and day are mentioned.
      (For detailed information, refer
      If year is between 1900 and 9999 (inclusive), Excel uses that value as the year.
      If year is less than 0, or greater 9999, then Excel returns the #NUM! error value.
      In your formula, the year is 1899, month is 12 and the day is 31.
      Hence, the date is returned as 31/12/3799. The numerical value of this date is 693962.
      Note that, the numerical value of one complete day 1 Jan 1900 is 1, since it the first day in the calendar which is used in Excel.
      The numerical value of the date 31 Jan 2016 is 42400, which means that it is the 42400th day after 1 Jan 1900.

      0*7 is zero, since any number multiplied by zero is zero. Hence this part does no activity at all.

      If, B2 is greater the 60, then it takes "B2 minus one" as the value. If B2 is less than or equal to 60, then it takes "B2" as the value.

      So our START DATE is = 693962 + 0 + (B2-1 or B2)

      The END DATE in the formula DATEDIF is "TODAY"

      The unit used “D”. So the result is given in number of days.

      The value of the start date is so large, that it is almost certainly going to be larger than the end date. The end date must always be greater than the start date, otherwise the Excel DATEDIF function returns the #NUM! error.

      With so many errors in it, I would not have used the formula in the current form. I strongly recommend editing the same.

      Vijaykumar Shetye,
      Goa, India

  21. ZAHIR says:

    Is there any excel formula available to convert hijri date to Gregorian??

    • Vijaykumar Shetye says:

      Dear Zhir,
      There is no function in the Excel function list, to convert Hijri dates to Gregorian.

      Type a date in Gregorian format and have Excel interpret it as Hijri date:
      Go to Custom Format and enter B2dd/mm/yyyy.
      The date will be displayed as Hijri date.

      I recommend that you read the relevant documents on Microsoft Office Support and understand the precautions to be taken before you attempt to use this format.

      Vijaykumar Shetye, Goa, India

    • abas khan says:

      Dear ZAHIR,

      for hijri date put in custom cell format B2dd/mm/yyyy and for Gregorian date
      put B1dd/mm/yyyy.

      you will find result.

  22. Mike Wilson says:


    I'm having a lot of difficulty creating a graph using the following data:
    12/20/2014 01:29.07
    2/7/2015 01:26.67
    2/28/2015 01:24.74
    10/25/2015 01:16.82
    11/7/2015 01:17.03
    11/21/2015 01:14.50
    11/28/2015 01:14.85
    1/9/2016 01:13.01
    2/6/2016 01:09.53
    2/13/2016 01:08.21
    3/13/2016 01:08.95

    Where column A contains dates and B contains times as minutes:seconds.hundredths

    I's like to have the times on the x axis if possible. I can get them on the y axis with a range, but I can't find an option to change the range if using x axis. Any help is greatly appreciated. Thanks.

    • Vijaykumar Shetye says:

      Dear Mike Wilson,
      If you are not finding an option to use x-axis, then probably you may not be using the scatter chart (or the X-Y chart).
      Kindly confirm that you are using the scatter chart.

      Vijaykumar Shetye, Goa, India.

  23. Tyler says:

    I am responsible for sending out a daily sales dashboard for my departments sales. I have a two tab worksheet. Tab one is graphical representation of tab twos formulas. On tab two I have three columns (date, actual sales, and goal). I am attempting to find a formula for tab 1 to automatically grab data from column b and c (actual sales, goal) based on today's date. For example if it is March 28th the formula will find March 28th sales and goal numbers on column b and c of tab 2.

  24. PREM NEUPANE says:

    Good Day!!!

    How can I convert the Hijri date to Gregorian date.
    I have tried different formula but do not work properly.
    Awaiting for your feedback... please

    Thanks & Regards.
    -Prem Neupane

    • Vijaykumar Shetye says:

      Dear Prem Neupane,
      There is no function in the Excel function list, to convert Hijri dates to Gregorian.

      To type a date in Gregorian format and have Excel interpret it as Hijri date:
      Go to Custom Format and enter B2dd/mm/yyyy.
      The date will be displayed as Hijri date.

      I recommend that you read the relevant documents on Microsoft Office Support and understand the precautions to be taken before you attempt to use this format.

      Vijaykumar Shetye, Goa, India

  25. dilip gandhi says:

    =DATE(YEAR(G6)+50,MONTH(G6),DAY(G6)) but it works only if i entered year first than month & than day
    But it can not work if i entered day month & year as i have to work with date perfectly in no of cases so it is not possible to make data entry of date in following order year first than month & than day there is chance of mistake while data entry if ther is solution pl give guidence

    • Vijaykumar Shetye says:

      Microsoft Excel does not use the format of the date while calculating, but the numerical value of the date. The calendar year begins from 1 Jan 1900. Hence 1 JAn 1900 =1
      31 Jan 2016 = 42400.

      When you type the formula,
      Excel detects that the year of 42400 is 2016, month of 42400 is 1 and the day of 42400 is 31. So I do not expect any error in the manner described by you.
      You may enter the date in any of the standard date formats available.

      I hope I have understood your query correctly.

      Vijaykumar Shetye,
      Goa, India

  26. Ryby says:

    Hi, I am trying to figure it a formula to figure out certificate expectation dates, one cert ends in 5 years and one ends in 10years the column I contains the date the certificate was completed and column J contains type of certificate ex: Cert Apple is 5years and Cert B is 10years. How do I create a formula to figure out when they are close to expiring 90,60,30 days out

  27. Gohar Ali says:

    March 31 2007 12.00 AM

    kindly tel me how to convert it in date format using formula in excel

    • Vijaykumar Shetye says:

      Option 1
      Select the cell,
      Go to Home - Format - Number - Date and
      choose 1 of the default date formats available

      Option 2
      Select the cell,
      Go to Home - Format - Number - Custom - Type and
      Type MMMM DD YYYY or any other date format required.

      It is generally advisable to use the default formats.

      Vijaykumar Shetye,
      Goa, India

  28. Rochelle says:


    If I have a start date and end date, what is formula to check if it is expired or not

    • Vijaykumar Shetye says:

      Dear Rochelle,
      Use the formula
      =If(today()>=A1,"Expired","Not Expired")

      I have used cell A1 for end date. Change the cell reference as required.

      Vijaykumar Shetye, Goa, India

  29. Tenneil says:

    I am trying to track training and have the cells change color as the expiration date approaches. So Equal Opportunity Training was conducted 01 Feb 2016 and will expire 01 Feb 2017. If I enter 01 Feb 2016 in the cell what formula do I need to enter to get the cell to turn red on 01 Feb 2017, turn yellow 01 Nov 2016, and be green from 01 Feb 2016 to 31 Oct 2016. And I understand this will most likely occur under conditional formatting.

  30. John Smith says:

    I have been try to find a formula to give a next start date. I have a create date and need to add 1 month to date to get my schedule date and the schedule date needs to be always 1 month ahead of the create date. any idea? thanks

    John Smith

    • Vijaykumar Shetye says:

      Use the formula

      where the original date is in cell A1.

      Vijaykumar Shetye,
      Goa, India

  31. EV says:

    Hi i have to fill a date for first 19 rows as 1-jAN-2016 and after that i have to fill 2-jan-2016 for another 19 rows ,like that, i have to fill for a year up to December by dates is there any formula or how i can do it in excel? is it possible ?

    • Vijaykumar Shetye says:

      Dear EV,
      Enter the following formula in cell B4
      In the cell above B4, i.e. in cell B3, enter the date 31 Dec 2015.
      In case you want the formula in some other row, then replace the 4 in the formula with the new row number.
      Example if you enter the formula in cell b10, then replace 4 with 10.

      There are many other ways of doing this activity. Since you have mentioned about formula, I have given a formula for the same.

      Vijaykumar Shetye, Goa, India

  32. Squirrelly says:

    A - B - C - D - E - F - G - H - I.

    Columns A to G are descriptions, but I need to know what the next PM date is (in column I).I am trying to get Columns H & I to determine the date one year apart.
    Can you help me out with a formula for this?

    • Vijaykumar Shetye says:

      Dear Squirrelly,
      Enter the following formula in cell I1 and drag it down.
      I have considered the PM date to be in cell H1.
      Format cell I1 to any date format required.

      Vijaykumar Shetye, Goa, India

    • hi i would like to know how it use the formula if Mr. A in date 01 June and Mr. B start on 06 June but Mr. C start on 20 June so person who start from 01 to 17 of month we open salary on 22 June but from 18 to end of month open on 22 July. says:

      hi i would like to know how it use the formula if Mr. A start on 01 June and Mr. B start on 06 June but Mr. C start on 20 June so person who start from 01 to 17 of month we open salary on 22 June but from 18 to end of month open on 22 July.

  33. Aneesh says:

    I have this scenario where in column A I have the date (04/25/2016) and in column B I have the time (12:46 PM)
    I am trying to find a formula where I can change the date and time when the time mentioned in the column B is 3:00 Pm or above and it should reflect the next day in the date column with 7:00 AM as the time in Column B.

    Example : column A - Column B
    04/25/2016 - 3:00 PM
    It should change to
    04/26/2017 - 7:00 AM

  34. Ronell says:

    How to Subtract the date in a cell, but if it has a same date it will compute as 1 day. Please Help.

    • Vijaykumar Shetye says:

      FORMULAS 1
      =IF(A1=B1,1,B1-A1) or
      where column B contains the final dates and
      column A contains the initial dates.

      FORMULA 2
      gives same result as formula 1

      FORMULA 3
      This formula considers the first date as a complete day.

      If the text "days" is required after the value, then use one of the following formulas, example
      FORMULA 4
      =IF(A1=B1,1,B1-A1)&" days"
      The result of formula 4, will not remain a number.

      Vijaykumar Shetye,
      Goa, India

  35. Scott says:

    I am trying to create a spreadsheet where it is giving back dates based off of the first date given. So say that B2 is a go-live date and B3 is a date specified by a formula given back based off of the date in B2. When B2 is erased, B3 turns to ####### because it now becomes a negative date. I can't seem to find a formula that will keep B3 blank until something is entered into B2.

    B2= a date
    B3 = B2-6

    This is basically a forecast on when things should be turned in prior to a go-live date.

    • Vijaykumar Shetye says:

      Use 1 of the below formulas as per your requirement
      =IF(B2-6<0,"",B2-6) or
      =IF(B2-6<0,"-",B2-6) or

      For NEGATIVE Values,
      First formula returns blank,
      Second formula returns dash (-) and
      Third formula returns 0 (zero)

      Vijaykumar Shetye,
      Goa, India

  36. DT says:

    I am trying to add ranges in new tab that needs to show year ranges,pls advice
    Example is
    if order is in 2014 so it show >2 year
    If order is in 2015 result should be >1 year

  37. SHEIKH says:


  38. Swaroop says:

    I am trying to subtract & add 2000$ to the total with in a specific period of say 2 months
    I have taken loan for 10000 on 1/1/16, I am going for leave for 2 months vacation on 2/02/16 so from this date onwards the total amount to be reduced by 2000/- & later after 2 months it should automatically added to become 10000/-

  39. ALEX says:

    I am trying to have my excel formulate each person on when 6 months is up on each of there training certificates once I enter a date in. I want it to automatically turn red once they are expired so I can notify them on when the last time they complete it.

    • Jess says:

      Highlight cells to be formatted (certificate dates).

      Go to Home > Conditional Formatting > New Rule.

      In the New Formatting Rule dialog box, click "Use a formula to determine which cells to format".

      Under "Format values where this formula is true", type the formula =(EDATE(B3,6))<TODAY() (where B3 is the top cell to be formatted. Even though only B3 is written, it will apply this to all cells separately).

      Click "Format".

      In the Colour box, select the red colour.

      Click OK until the dialog boxes are closed.

      Dates that are now past six months should be in red.
      I hope this helps.

  40. KM says:

    I have cell A1 that is formulated to give an answer for duration (year, month, day) as follow
    =IF(DATEDIF(F17,$G$17,"y")=0, "", DATEDIF(F17,$G$17,"y") & "Y ") & IF(DATEDIF(F17,$G$17,"ym")=0,"", DATEDIF(F17,$G$17,"ym") & "M ") & IF(DATEDIF(F17,$G$17, "md")=0,"", DATEDIF(F17,$G$17, "md") & "D")

    Example of results:
    11M 19D
    3Y 2M
    10Y 8M 21D

    I need a new cell which will give answer to the following criteria;
    If duration is less than 2 years = A, if duration is more than 2 years = B and if duration is more than 5 years = C

    Is there a formula to this?


  41. hasanov says:

    Hello, I would like to know how to calculate the requested room nights per day, for example to achieve the given budget for the month.

    thank you

  42. Jess says:

    I would like to know if there is a formula that can continuously calculate dates: patients at a clinic are reviewed every 13 weeks from their admission date, with no set time for stopping the reviews. I would like a column to show the next review date, after the current review date has been passed. It will be a tool used so nobody misses out on their review (Dates here are dd/mm/yyyy).
    For example:
    Patient Name Admission Date Next Review Date
    John Doe 03/03/2016 02/06/2016
    Jane Doe 18/05/2014 14/08/2016

    Thank you.

    • Vijaykumar Shetye says:

      Dear Jess,
      If the admission date is in B2, then use the formula in C2 and drag it down.
      =B2+13*7 or
      Change the format of the cell to the required date format.

      Vijaykumar Shetye, Goa, India

  43. Ahmad says:


    I am calculating pension between two dates on yearly basis for staff whom are leaving the organization. I could use this formula to do that but, the employee is doing the calculation manually so it shows slight difference.

    Can anyone help please!


    G13 is joining year;
    P27 is current salary;
    U13 is separation date;
    DATE(YEAR(TODAY()),1,1))))is January 1, of each year;
    The Salary is AFS 20,000.00 per month;

    Based on this formula the pension amount from January 1, 2016 to May 31, 2016 is AFA 8,273.97 and based on manual calculation it is =AFA 20000/12*5= AFA 8,333.33 and difference is AFA 59.36

    Your soonest rely will be highly appreciated!


  44. H pandeya says:

    how to + n _ date format in excel 12/11/2016

  45. J Rogers says:

    Hi there
    I'm trying to create a spreadsheet in excel that will highlight when a supervision is due

    For example - I want cell B2 to change to red 60 days after 01/01/2016
    I then want cell C2 to change to red 120 days after 01/01/2016

    I've been looking for a conditional format for weeks and am struggling

    Any help would be great

    • Vijaykumar Shetye says:

      Dear J Rogers,

      Select cell B2,
      Go to Home - Conditional Formatting - New rule - Use a Formula to determine which cell to format - Format values where this formula is true
      Enter the formula
      Go to format - Fill
      Select Red colour
      Click OK twice

      Repeat the same for C2 and other cells
      The formula for C2 would be

      Vijaykumar Shetye, Goa, India

  46. Sandy says:

    I need a column to determine who is a minor that (using YEARFRAC function) would automatically note who is minor.
    I attempted to inbed the YEARFRAC formula for age into an IF statement and it did not work. Here's my invalid formula: =IF(INT(YEARFAC(G4,TODAY())),'Minor', )

    • Vijaykumar Shetye says:

      Dear Sandy,

      (1) Kindly specify the age limit for declaring a person as 'Minor'.

      (2) Also mention if it is to be considered from 1Jan of any year or as per the current date.

      When posting a question, please be very clear and concise.

      Vijaykumar Shetye, Goa, India

  47. Maan says:


    I am currently updating my company's Excel files. I wanted to add the day's date in a cell if a project is marked completed. ive' used the following formula: =IF(ISTEXT(U:U), NOW(), ("")). it worked but I am facing a problem. the projects that were marked completed had the date in the cells next to them but the dates keep changing to today's date or the current day i open the excel file. can you please help me with telling me the right formula to add an unchanging date using IF function?

    Thank you,

  48. Guy60 says:

    I ma trying to develop a schedule that will be comprised of six 10 day parts depending on the start date and holidays. I want to be able to change the start date and have the six parts provide the start and end dates for each part. To clarify, if there is a holiday in one of the parts I want to have that day subtracted from the total (instead of added) so that there would be fewer working days (than 10) and the dates would reflect this difference for any parts that have holidays otherwise the part should be 10 days. Example: Start June 29, 2016 10 days =WORKDAY(June 29 2016,10, July 4, 2016) returns July 12 I need for it to return July 8, 2016 which is 10 working days from June 29 minus 1 day for the holiday.

  49. Praharsh Tiwari says:

    Dear Sir,
    How to calculate date value between two date.
    Vendor Name Item Qty Date
    AB POLE 20 10/5/2016
    AC POLE 50 15/05/2016
    ABC POLE 20 18/05/2016
    AB POLE 10 20/05/2016
    AC POLE 25 22/05/2016
    ABC POLE 30 24/05/2016
    ABC POLE 10 26/05/2016
    AB POLE 20 28/05/2016

    AB 50
    AC 75
    ABC 60
    Above the date sheet, i want the result if the qty purchase 50 pcs by ABC than shown date interval i.e.18-05-2016 to 26-05-2016

    • Vijaykumar Shetye says:

      Dear Praharsh Tiwari,
      Use the following Array Formula
      =B12&" "&SUMIF($B$3:$B$10,B12,D3:D10)&" "&TEXT(MIN(IF($B$3:$B$10=B12,$E$3:$E$10,999999)),"dd-mm-yyyy")&" to "&TEXT(MAX(IF($B$3:$B$10=B12,$E$3:$E$10,0)),"dd-mm-yyyy")

      I have entered it in C12. Change the cell references as required.
      To Enter an Array Formula, you have to click Control+Shift+Enter instead of Enter.

      A B C D
      Vendor Name Item Qty Date
      1 AB POLE 20 10/05/2016
      2 AC POLE 50 15/05/2016
      3 ABC POLE 20 18/05/2016
      4 AB POLE 10 20/05/2016
      5 AC POLE 25 22/05/2016
      6 ABC POLE 30 24/05/2016
      7 ABC POLE 10 26/05/2016
      8 AB POLE 20 28/05/2016

      10 AB AB 50 10-05-2016 to 28-05-2016
      11 AC AC 50 15-05-2016 to 22-05-2016
      12 ABC ABC 45 18-05-2016 to 26-05-2016

      Vijaykumar Shetye, Goa, India

  50. Anja says:

    Hi there,
    I need some help with calendars. I manage rent payments and one of the tenants does not speak English well.
    I have the dates she paid her rent in column A and it starts from 2014 until now.
    I would like to show her a calendar indicating, when she did not pay on time.
    Therefore I would need a calendar like
    S M T W T F S
    1 2 3 4 5 6
    7 8 9 ......

    and a formula which tells the calendar which cell to highlight for rent payments, means the dates from column A.

    Is there an option for this?

  51. ravish says:

    please help me how to calculate the days from 15-5-2016 to 26-5-2016 excluding holidays falling in the first 5 days from 15-5-2016 to 19-5-2016

    • Vijaykumar Shetye says:

      Dear Ravish,
      Use the below formula

      Cell references are as follows
      Start date in cell A1,
      End date in cell A2,
      List of holidays in cells B1 to B100.
      Change the cell references as required.

      Vijaykumar Shetye, Goa, India

  52. Muzzamal Azeem says:


    Thank you for giving such wonderful tips, i want to calculate daily productivity on excel like column A date column B references of accounts attended, how i can calculate number of accounts attended on a particular date.


    • Vijaykumar Shetye says:

      Dear Muzzamal Azeem,

      Use this array formula is column E
      Array formulas are entered with Control+Shift+Enter instead of Enter.

      List of dates is in column D, and your data is in column A to B, as you have mentioned in your post.

      A B C D E
      1 01/01/2016 A/C 01-Jan 2
      2 01/01/2016 A/C 02-Jan 4
      3 01/01/2016 03-Jan 4
      4 02/01/2016 04-Jan 5
      5 02/01/2016 A/C 05-Jan 3

      Vijaykumar Shetye, Goa, India

  53. Giovanni says:

    I'd like to know if this is possible.
    I'm trying to get how many days are my computers hired in certain months.
    Say, 1 computer is hired from June 10 to July 20, 2016.
    Is their a turnaround/ formula on how to get the days hired in june and july in separate columns.
    So for june I get 20 days hired, and july 20 days hired.

    • Vijaykumar Shetye says:

      Dear Giovanni,

      (1)How is your data organised? Are the start dates and end dates in 2 columns? Is the name or ID of the computer mentioned in another column? Can you send the details of the data in brief?

      (2) In the period 10 June to 20 July, June has 21 days. Is the first day to be ignored?

      When posting a question, please be very clear and concise.

      Vijaykumar Shetye, Goa, India

  54. anusha says:

    How to calculate Total Exp( Sample Format : 10 Years 6 Months & 12 Days)
    When i'm using DATEDIF() function it gives only year
    please give me the idea how i can get Sample Format : 10 Years 6 Months & 12 Days

  55. nessa says:

    I would like the return value of the cell to be the start of the current year if an employee started prior to the year starting and if they started within the year to return their hire date. Can you assist?

  56. Dennis says:


    I hope you can help me with my problem, i have a Received date and Fixed date in Cell A1 and B1, i need to calculate if A1 starts at <or=8:00 AM to 4:00 PM,maybe we can put it in C1 as Yes and for No. Then another calculation is closing Date if all Yes in C1 is closed at 5:00 PM, in No Calculate if closed within 24 hours. This except Sat and Sun.


  57. Sarathraj R says:


    equation for showing a particular date for a dategroup(means a week or a countious 5 days, workdays etc)

    eg:01-05-2016 to 05-05-2016 is shown in a cell as 01-05-2016.

  58. harish rawat says:


    i need some help/tip in calculating difference between two dates in one cell. i also try datedif formula but in office excel 2007 is don't exists. i am also look for help for converting 'ddmmyyyy'(7121985/31052005) in 'dd-mmm-yy'(07-Dec-85/31-Mar-05). I required all this for excel sheet 2007. Please help me.../////

  59. Kristine says:

    HI there,

    I am currently creating a schedule where I want to go back 1 week -10 daysfrom a numerical date in a cell, but within that one week-10 days I want excel to choose the wednesday furthest back, how would I go about doing that.

    Say my due date is September 1, 2016, going back 10 days will take me back to August 22 (monday). I want the cell to compute Wednesday, August 24. I have different dates to set up so it'll never be consistent like 5 days before etc. and I don't want to have to change for each instance.

    Any help would be greatly appreciated!

  60. kit says:

    hi ld like to ask what formula should I use for this. in E2 I have 01/01/2016. I want to create a formula so it will be converted to January in F2. what should I use?

    • Hi Kit,

      You can do this in 2 ways:

      1. In F2, enter the formula =E2, and then apply the custom date format: mmmm
      You can find the detailed explanation about custom formatting here: How to create a custom date format in Excel.

      2. Use this formula: =TEXT(E2, "mmmm")

      The difference between the above approaches is as follows:

      1. Changes only the visual representation but the underlying value in F2 is the full date and it can be used in other calculations.

      2. The value in F2 is a text string and not a date.

      • Kit says:

        OMG! You are amazingly super smart. The 2nd option worked for my need and report :). I really appreciate your help. you are AWESOME!

  61. Emil Paul says:

    Hi there

    I have a sheet that we use for targets. Now if you pick a month it should run from the 11th to the 10th of the following month. My formula works fine but when a month has 30 days or 29 or 28 days it will run till the 12th or 11th instead of stopping at the 10th. How do I limit it to end till the 10th of each month.

  62. Tripurari Singh says:

    it is very good example to adding the formulas in Excel sheets and very good site to learn about excel.

  63. Jan says:

    I have this bi-weekly payroll start and end date.
    I need a formula to calculate number of pays if I select a range from start and end date.
    for example I want to see number of pays from start date 5/23/16 to 8/14/16 end days.

    Start Date End Date
    12/21/2015 1/3/2016
    1/4/2016 1/17/2016
    1/18/2016 1/31/2016
    2/1/2016 2/14/2016
    2/15/2016 2/28/2016
    2/29/2016 3/13/2016
    3/14/2016 3/27/2016
    3/28/2016 4/10/2016
    4/11/2016 4/24/2016
    4/25/2016 5/8/2016
    5/9/2016 5/22/2016
    5/23/2016 6/5/2016
    6/6/2016 6/19/2016
    6/20/2016 7/3/2016
    7/4/2016 7/17/2016
    7/18/2016 7/31/2016
    8/1/2016 8/14/2016
    8/15/2016 8/28/2016
    8/29/2016 9/11/2016
    9/12/2016 9/25/2016
    9/26/2016 10/9/2016
    10/10/2016 10/23/2016
    10/24/2016 11/6/2016
    11/7/2016 11/20/2016
    11/21/2016 12/4/2016
    12/5/2016 12/18/2016

  64. Vivian says:

    Hi, I need a formula for dates.
    For example,
    11/1/15 through 2/1/16
    11/1/15 through 3/1/16....etc
    I need it where the first date does not change but the latter date changes. When I insert 11/1/15 - 2/1/16, I believe Excel takes the "through" as a subtracting sign. How do I fix this?
    Thank you!

  65. velmani says:

    21/01/2016 i want to twenty first january two thousand sixteen

  66. bhavik says:

    i want to calculate date with the numbers then want the answer in date.
    ex. date is 27/06/2016 then i add 08 in the other sell i want the ans in date format like 05/07/2016. plz help me

  67. Elyes says:

    I have found some valuable info on this great site, but when I try to put what I find into use I keep getting error messages. I'm guessing due to the complexity and my lack of knowledge it's simply not going to work just piecing together the formulae I need from the info I find here.
    I need a formula to work out compliance to a medical standard, the cells are as follows;
    C1 is date of birth
    F1 is date of last medical
    I need to return a yes or no in G1.
    If the subject is over 35 years old the date in F1 has to be less than 5 years ago to return yes. If the subject is under 35 years old F1 has to be within the last 10 years to return a yes value.
    I have been going round in circles with not much luck in creating the right formula.
    Appreciate any help you can provide!

    • Elyes says:

      My only working solution is the formula below, but this does not account for the age of the subject. I can't get the link to the age to work correctly.
      =IF(DATEDIF(F1, TODAY(), "y")<5,"YES","NO")

    • Hi Elyes,

      If my understanding of the task is correct, the following formula should work a treat:

      =IF(OR(AND(DATEDIF(C1, TODAY(), "y")>=35, DATEDIF(F1, TODAY(), "y")<5), AND(DATEDIF(C1, TODAY(), "y")<35, DATEDIF(F1, TODAY(), "y")<10)), "yes", "no")

  68. peter says:


    I have two dates in two different cells (A1 = 4/12/1993 and B1 = 04/05/1993) and i want to verify if they fall with the same quarter. If two dates are within the same quarter, the data "passed" if outside of 89 days, it fails...

    thanks for your help....

  69. Jams says:

    Hello, can anyone help me what is excel formula if the starting date will tell it is overdue in: equal or less than 3 months, greater than 3 months, greater than or equal to 6 months?

  70. aman says:

    I want to change the date format 7/30/2016 in dd-mm-yyy and it should be 30-07-2016. Please help

  71. Mike says:

    Hi there. Great page but hoping you can answer my query?

    In column A I have a date in format DD/MM/YYYY and want to convert this to be displayed in the MM/DD/YYYY format (or the serial number relating to the date) in column B. I have used Text To Columns to do this but this will not update column B if the value in column A is changed or if a new date is added in the next row down.

    Is there any way I can do this using the DATE, DATEVALUE or any other functions?

    Thanks :)

    • Hi Mike,

      I can suggest the following 2 ways:

      1. =TEXT(A1, "mm/dd/yyyy") - the result will be a text string, but not a date.

      2. Use this simple formula =A1, and then apply the desired format to the 2nd column (select the column, right click, click Format Cells, select Custom under Category, and type mm/dd/yyyy in the Type box). The resut will be a fully-functional date in the desired format.

  72. BERNARD says:

    good day, any one to help me to have a formula 031214H August 2016 convert to 08/03/2016... thanks you

  73. bong says:

    or have a formula 031214H August 2016 convert to 08/03/2016... thanks you

    • Hi BERNARD,

      Try this formula:
      =DATEVALUE(LEFT(A2,2)&"-"&MID(A2,SEARCH(" ",A2)+1,SEARCH(" ",A2,SEARCH(" ",A2)+1)-SEARCH(" ",A2)-1)&"-"&RIGHT(A2,4))

      Where A2 is the cell containing the string 031214H August 2016. Also, be sure to apply the desired date format to the formula cell.

  74. Janice says:

    I am trying to calculate an end date using a start date and the number of weeks. EX start date 9/5/2016 need to go out 52 weeks = what would end date be? Is this type of calculation possible or do I need to convert Weeks to days?

  75. Ronald says:

    Im trying to calculate a prison sentence of 24months(2years) commencing today 8/8/2016. Release date is 8/8/2018, however a remission of one third(1/3) was deducted from 24 months. Can any excel elite calculate the new release date?

    Sentence date: 8/8/2016
    period sentence: 24 months
    Release date: 8/8/2018
    Remission: 1/3
    New release date??????

  76. sam says:

    I want these remain dates in this row & Column, Plz help me how I can put the formulas
    1-Aug-16 10-Aug-16 11-Aug-16 31-Aug-16
    2-Aug-16 11-Aug-16 12-Aug-16 31-Aug-16
    3-Aug-16 12-Aug-16
    4-Aug-16 13-Aug-16
    5-Aug-16 14-Aug-16
    6-Aug-16 15-Aug-16
    7-Aug-16 16-Aug-16
    8-Aug-16 17-Aug-16
    9-Aug-16 18-Aug-16
    10-Aug-16 19-Aug-16

  77. shan says:

    I want subtract two date but some cell getting blank that time i wants if cells are black their getting today values

  78. Sarah Nessler says:

    I am trying to find a formula that will take D28(which is a date) and then will compare to a list of dates(US Holidays that the list has a defined named as Holidays_US) and if it is in that list then I want to return D28 plus a day and if it isn't in that list then I want to return D28+365. I can't figure it out please help if you are able to. Thank you!

  79. CJ says:

    I need to create a formula that calculates the number of sick days an employee gets from hire date (1 day at 6 mos, 2 days at 1 yr, 3 days at 2 yrs, 4 days at 3+ yrs) based upon the current date at any given time [i.e. TODAY()]. Can you please help me? Thank you!!!

  80. Edward Lamin says:

    I am creating dropdown menu for the start date and end date of all projects, but would like to reflect in the column. How do I proceed? Example 12/23/2015 - 12/22/2017.

  81. Tania L says:

    Hello I am trying to identify due dates for training. We have several different trainings that are required, and they all have different frequencies (i.e. annual, bi-annual, semi-annual, etc). Is there a way to identify when the next due date would be if I have one column that has the date the training was taken, a column showing frequency (annual, semi-annual, etc.) and then a column with new due date?

  82. Matthew says:

    Hi , i am trying to convert a cell with the following text 1/18/2016 10:00:00 am to 2016/1/18 . but i cannot seem to make it work with datevalue. Kindly help ..

  83. Stacie says:


    I am trying to find a formula to calculate the following:

    Look at the date in cell A2
    Look at the date in cell A3
    Figure out which is the earliest date
    Then subtract 4 weeks (or 30 days) from the earliest date in either cell.

    Is this possible?


  84. Tom T says:

    I'm sorry if this has been covered previously!

    I am calculating the number of days between two dates, using one column for the start date and a second column for the end date. the third column is the number of days between the two dates. All is well until the dates span the February/March period during leap year. In that case, the calculation is incorrect by a day. Is this a bug in the Excel "DAYS" function? Is there a way around it?



  85. Tom T says:

    In answer to Stacie (83), I think

    =min(date1,date2)-30 should work if you format the cell as a date.

    I don't know if that will meet your needs, but the math seems to be good.



  86. suria says:


    can somebody help how to make tabel for this appoitment date


    date - MAIN DATE { 29-aug-2016(monday)}

    week month
    1. 05 sept 2016 26 sept 2016
    2. 12 sept 2016 31 oct 2016
    3. 19 sept 2016 28 nov 2016
    4. 26 sept 2016 26 dec 2016
    5. 03 oct 2016 30 jan 2017
    6. 10 sept 2016 27 feb 2017

    the week date i can do but the date for month i can't do
    b'coz the month date must +/- 3 day from main date (29 AUG 2016)...

    can somebody help me thanks..

  87. farra says:

    Hi Ablebits,

    I need help on dates formatting. I have a list of dates as far as 2014 until today. I would like to highlight dates that are 30 days from the date in the cell, 60 days and 90 days. I've been Google-ing answers for days, but to no success. Please help.

  88. COS says:

    How to calculate the date of retirement of an employee after completion of 60 years.

  89. prasad says:

    how do I find a date after some days of a specific date.
    for example,let installation date of a product is 5/15/2013
    and the life span of that product is 1500 do I find out the replacement date of that there any excel function for that.?

  90. Shabir Hussain says:

    Please help. I'm subtracting two dates to calculate number of holidays taken. The two dates are in two separate columns with answer in the third column e.g.
    A12 Date leave started
    B12 Date leave ended
    C12 Formula answer with No of Days Leave Taken.

    The problem arises when someone takes 1 day's leave; my formula says 0 Days. For example:
    A12 Date leave started 21/05/2016
    B12 Date leave ended 21/05/2016
    C12 Formula answer with No of Days Leave Taken. Result is 0 days. I want it to say 1 day's leave taken.
    Thank you

  91. Lindsay Terpstra says:

    Hello! I have a project at work where a deficiency needs to be completed by 7 days after the original deadline. I am looking for a formula where a column is turned red after 7 days-does this exist?? :s

  92. Jan Bass says:

    You truly are incredible. I need to determine if a date in a range exists. If it exists then I would like it entered it into another cell. Is there a simple way to do this?
    Thank you!

  93. GAURAV says:

    hello i need to get some formulae to calculate data that falls under particular month in a sheet with different columns of date for eg.
    we got columns as
    customer address date1 date2 date3 date4
    gaurav india 1/4/15 24/5/15 21/7/15 19/8/15
    like this upto 12 date columns and there is no limit for the no. of rows.
    so i need some help if i want to get data for the month of may, it shows me this customer detail. really appreciate your response…. thanks

  94. Nab says:

    If I have a start date and end date are as per Hijiri calendar, to check if it is expired or not so how i can apply this =If(today()>=A1,"Expired","Not Expired")

    i tried below function but nothing changes

    ype a date in Gregorian format and have Excel interpret it as Hijri date:
    Go to Custom Format and enter B2dd/mm/yyyy.
    The date will be displayed as Hijri date.

  95. Carrie says:

    I'm trying to figure out when an employee is eligible for enrolment in our pension plan. I have the following:
    C3=start date with company
    D3=eligibility date=6 months after start date
    The plan didn't become effective until July 1, 2013 so I'm looking for a formula that says if C3 is less than or equal to July 1, 2013 then use July 1, 2013, if not then use C3 plus 6 months.
    Any help would be fantastic.

  96. usha says:

    I want to change this date format 1/1/2016 12:00:00 AM
    into fiscal years, 15-16, can anyone help.

  97. Lorie says:

    I am working on a training matrix. I have a column with first aid. If it is past current date it should go red. The first aid is valid for 3 years I need it to go yellow 3 months before it is set to expire using date in column and not current date. So it it is valid it is green, if it is past due it is red and if it is 3 months before it turns red or past due it is yellow to warn me to get them trained. Any suggestions.

    Second column is training that is due annually. So red if past due, green if ok and yellow again three months before date in column's one year mark as a warning to get them re-certified.

    Can anyone help with these formulas. Any date formula tips for training matrix are welcome. Thanks

  98. shinkoru says:

    hi i need to formula this.

    i have a start date lets say:

    start date : 1/9/2016
    i applied to be on leave for months

    what is the end date should be?

  99. lukas says:

    Hi There

    I am trying to calculate the number of days between two entries. I am using the following formula.


    The formula works but when I use a blank cell (F4) the formula stops working.
    Can anybody help please.

  100. Lorri says:

    HELP! I just want to know if Excel has a way to make a column with each cell representing Week 1 - then the dates of that week for 2017. For example the cell would read: Week 1 - January 1-7 (or better if it was just work week, Jan 2-6). Can this be done??? Please help me.

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