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 17. Total comments: 684

  1. If I don't have "B" then I want my result to be a "0".
    How can I do it???

    Example:
    If I want to get the days since something is open and one of the dates is empty.

    "A" Date of report (I will always have this information)
    "B" Date the action is open (I will not have always this information)

    my formula is

    ="A"-"B" = days since it has been open.

    If I don't have "B" then I want my result to be a "0".
    How can I do it???

    1. I found the answer :-)

      =IF(B="", 0, A-B)

  2. Hi

    I need to set a formula which help me to auto calculate the expiry date
    eg : start date is 6 Apr 15 and the expiry date is 2.5 years later

    and i will use this formula for the other cells
    do i need to put the $ so that i can just drag it down

    thanks

  3. I need a formula to add a year to date if a specified field has "Y".

    so if field A1 equals "y" then add a year to the date on A2.

  4. Hello,

    Since a week i am struggling with an Increment formula,
    Im using formula which shows as
    =IF(AA2=0,0,(F2)*VLOOKUP($AE2,ML!$F$26:$G$29,2,0))
    Here: AA2 is the End of Contract date
    F2 is the Basic Salary
    Vlookup is the range of Category(Doctor,Admin,Nurses,Paramedical staff) which define the percentage in Increment.

    Right now i have months in different coloumn
    i want this formula to show only for those people whose dates will reflect on the End of Contract months.

    for me this formula is effecting in all the months.

    Thanks in Advance

  5. Hello,

    Can you please help me out with the following scenario:

    I have an expiry date of 30-11-2016 I want to see on next column the date if I subtract 30 days on it e.g. next column should read like 31-10-2016.

    Thanks in advance.

  6. Hi,

    I am looking for a excel formula.
    Working on incentive program for employees. I am using GDocs for tracking the employee performance.

    I am having start date and end date. With the help of start and end date I want to pay the incentives every month. Once the project reaches end date next month should be "0".

    Kindly let me know how to write the formula for this.

    Note: All the docs are in GDocs & 2 different files.

    Thanks in advance.

    Regards,
    Sharath Babu S

  7. I WANT TO FIND REMANING % FROM 100% WHEN GIVEN % IS 17.09

  8. Hi I am trying to create a spreadsheet for vehicle finance showing vehicle, purchase date, purchase price, total interest, number of monthly payments, number of payments remaining, monthly payment, outstanding finance.

    The bit I am struggling with is trying to create a formula for a cell to work out how many months are remaining. I know what I want it to do which is work out how many months are between the purchase date and end date using the current date if that makes sense.

  9. i have a leads excel (clients) and i plan to calculate how many leads we got:
    -yesterday
    -today
    -last7days
    -this month
    I have P column that have "7" standard possible answers; like the state of the lead. I need a formula for each state of the lead to count the nr of leads for the above time periods.

    Thanks,
    I really appreciate this answer i tried for one week to do it

  10. = COUNTIFS(P2:P8,"Waiting List",I2:I8,TODAY())

    tried this one and not working as well is saying 0

  11. I tried like this:
    =COUNTIF(I2:I11,"TODAY()") + COUNTIF(P2:P11,"=Waiting List")

    but it's not taking in count the Date; just sum all of them matching "Waiting List"

    Thanks,
    Traian

  12. i have a leads excel (clients) and i plan to calculate how many leads we got:
    -yesterday
    -today
    -last7days
    -this month
    I have P column that have "7" standard possible answers; like the state of the lead. I need a formula for each state of the lead to count the nr of leads for the above time periods.

    Thanks,
    I really appreciate this answer i tried for one week to do it.

  13. if i entered date by using formul =Today() the output i want same date -1 but if this date occurs on sunday it should be -2

  14. Hi Mam please let me know if 1 serial nos receive in two different date then whose formula use in excel

  15. Sum of Column values based on a particular row(person) like On the basis of specfic value like date Oct-2016 it gives us the Sum of other column which we select.

  16. I have two dates in two separate column and I want to have which ever date comes due first to populate into a new column. How do i formulate that?

    1. Hi Ryan,

      Assuming you are comparing dates in columns A and B, the following formula should work a treat:
      =IF(A2

  17. to get the result but without the last 6 months of this account code (supplier)

  18. hi,
    I want to exclude 6 months from total amount of a special account for making an aging report so I want the result over 180 days of base amount but without 6 months

  19. I have cells formatted as text with a month & day present. I am trying to use the current date to add the current or next year depending on month & day. So for example today's date is 10/10/2016, and I have 2 inputs, 7/1 & 1/1. I'd like to format as 7/1/2016 & 1/1/2017. Any suggestions?

  20. For the past year I have been using a formula to keep track of when my patients are due for their next visit. For example, if I visited today and they are due again in 4 weeks, I would plug in today's date and the formula gave me a date 28 days from today. The formula I was using was =SUM(column and row of today's date,number of days to next visit). So it might look something like this: =SUM(C3,28). That was working well up until a few weeks ago when all of a sudden, the projected date was coming up as a series of pound signs (#########). Has something changed with Excel?

    1. Yes, the column is to narrow

    2. Never mind. My future date column was too narrow for double digit months. When I widened it, the pound signs turned into the date. :-S

  21. how to write date formula

    1. date formula ctrl+:
      time gormula ctrl"

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

  23. Hi There

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

    =IF(F4='''',NETWORKDAYS(E4,TODAY()),NETWORKDAYS(E4,F4))

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

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

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

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

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

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

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

  30. Hello,
    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!

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

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

  33. 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 days.how do I find out the replacement date of that product.is there any excel function for that.?

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

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

  36. hai

    can somebody help how to make tabel for this appoitment date

    example;-

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

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

    HTH!

    Tom

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

    Thanks!

    Tom

  39. Hello,

    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?

    Thanks!

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

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

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

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

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

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

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

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

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

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

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

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