Comments on: Using DATE function in Excel - formula examples to calculate dates

The tutorial explains the syntax and uses of the Excel DATE function and provides formula examples for calculating dates. Continue reading →

Comments page 19. Total comments: 684

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

    1. Hello Tania,

      To help you better, we need a sample table with your data in Excel and the result you want to get.
      You can email it to support@ablebits.com. Please add the link to this article and your comment number.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  14. Hello,

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

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

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

      1. Works brilliantly! Exactly what I was after. Many thanks.

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

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

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

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

  19. Hello,
    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

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

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

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

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

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

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

  24. hi,

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

  25. Hii

    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.

  26. Hi,

    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.

    Regards,
    Dennis

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

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

    1. Hello Anusha,

      You can use the following formula:

      =DATEDIF(A2, B2, "y") &" years " &DATEDIF(A2, B2, "ym") &" months & " &DATEDIF(A2, B2, "md") &" days"

      Where A2 is the start date and B2 is the end date.

  29. 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.
    thanks!

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

  30. Hi,

    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.

    Regards,

    1. Dear Muzzamal Azeem,

      Use this array formula is column E
      =SUMPRODUCT(IF($A$1:$A$100=D1,1,0),IF(LEN($B$1:$B$100)>=1,1,0))
      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

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

    1. Dear Ravish,
      Use the below formula
      =NETWORKDAYS(A1,A2,B1:B100)

      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

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

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

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

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

  35. Hello,

    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,
    Maan

  36. 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', )

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

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

    1. 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
      =B2+60
      Go to format - Fill
      Select Red colour
      Click OK twice

      Repeat the same for C2 and other cells
      The formula for C2 would be
      =B2+120

      Vijaykumar Shetye, Goa, India

  38. how to + n _ date format in excel 12/11/2016
    10/12/2011

  39. Hi,

    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!

    =IF((YEAR(G13)<YEAR(TODAY())),(P27/365*(U13-DATE(YEAR(TODAY()),1,1))))

    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!

    Ahmad

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

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

      Vijaykumar Shetye, Goa, India

  41. 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. 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:
    6M
    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?

    Thanks.

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

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

  44. I am trying to subtract & add 2000$ to the total with in a specific period of say 2 months
    Example
    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/-

  45. PLZ HELP ME SIR/MADAM WHEN EXCEL SHEET DATE COVERT TO DATE BUT 30 DAYS FORWARD AUTOMATIC BUT WHY PLZ REPLY THANK YOU.

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

    1. Hi!

      You can use a formula similar to this:

      =IF(YEAR(A1)=2014, ">2 year", IF(YEAR(A1)=2015, ">1 year", ""))

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

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

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

      Vijaykumar Shetye,
      Goa, India

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

    1. 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
      =MIN(B1-A1,1)
      gives same result as formula 1

      FORMULA 3
      =B1-A1+1
      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

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

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

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

    2. Dear Squirrelly,
      Enter the following formula in cell I1 and drag it down.
      =DATE(YEAR(H1)+1,MONTH(H1),DAY(H1))
      I have considered the PM date to be in cell H1.
      Format cell I1 to any date format required.

      Vijaykumar Shetye, Goa, India

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