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

  1. Hi,

    I have to calculate percentage between two dates and the formula I'm using is =(MIN(TODAY(),F2)-E2+1)/(F2-E2+1) which is giving me the desired result. However, when I do not enter any dates in E2 and F2, I get a default result of 4272000% in cell G2.

    I want G2 to be blank when no information is available in E2 and F2, Also it should be blank if I enter dates in one of the cells E2 or F2 or even postdates.

    I would really appreciate if a formula could be devised in coordination with the above percentage formula to achieve the desired results.

    Thanks in advance

  2. I have a start date in A1 and End date in B1 of Construction project, I want first the formula calculate the number of days overdue with today date, and then check if the end date is greater than today then return "Not Due" otherwise calculate number of days overdue.

  3. I wondered if anyone could advise how to add fifteen days to a date and if more than return 'no' in another column with a count of how many days? Similarly with anything less than so for example:

    Greater than:
    Col A
    01/01/16

    Column b
    17/01/16

    Column C
    'No - 1 day'

    Less than:
    Col A
    01/01/16

    Column b
    15/01/16

    Column C
    'Yes'

  4. Great site! I am need of help with a specific date formula. It is as follows:

    I have a specific date that is calculated based on lead times in manufacturing. From that, I have a plant actual ship day of the week. Starting with the specific date, I need to calculate the next plant shipdate as an actual date, not day of week. Is there a formula for doing this?

    Example:
    Specific Date: 12/21/2016
    Plant Ship Day: Tuesday (depending on plant, this can be any day of the week so I have created a drop with all 7 weekdays to chose from)
    Needed: Next available ship date. In this example, 12/21/2016 is a Wednesday so the formula to determine the next Tuesday should equal 12/27/2016.

  5. Hi All,

    I need a formula for create a list of sequential dates.

    I have 2 slicer"Month" and "year" and holiday list also. if i select particular month and year from slicer, i need the end to end dates without holiday list

    For example: If select 2016 and Feb, I need dates without holiday list in column a1 2/1/2016 a2- 2/2/2016 a3- 2/3/2016------ last column 2/29/2016

  6. Im need a formula thta will add 6 month to a date in in cell A2 but if there is no date to return a zero

  7. Hi,

    I'm trying to get number of days between two dates. There are a number of ways to do it, but I'm not able to find one to suit my purpose to calculate vacation days!

    Eg: Cell A2 dates 01/12/2016 and cell B2 10/12/2016. So the person is on leave from Dec 1 to Dec. 10 which is 10 calendar days, but all the formulas I know show it as 9 days.

    Please help.

    Thank you

  8. Hi,

    How to calculate specific date in the next 5th years from a specific date or today's date?

    Regards,
    Santosh

  9. Hello,
    I want to audit if a form was present by the 30th day and by the 90th day. I want a clean spreadsheet to use as a template every month. When I use =B3+30 in one cell and =B3+90 in another cell it returns 1/30/00 since B3 is blank. I want this as my template every month and I would like the formula cell empty until B3 has a value.

    Thank you!

  10. 21/nov/2016 how it will be done with date function or any other function.

  11. Hello ,

    I am trying to develop a spreadsheet that I can input the date and automatically generate the date of the 6 month review ... is that possible ?

  12. Hello, I would like to have an automatique date put in when i put a X in a colume. But I do not want that date to change once it is put it in. I tried this but once we change date the date in the colume changes to and i don't want that.

    =IF(G4="x";NOW()) -- changes date the next day
    =IF(G4="x";Today())-- changes date the next day

  13. Dear All
    I want to maintain a expense sheet which contains cash expense and credit expense but the problem is about the preparing for merge of cash and credit how i can please let me Know

  14. Hi,
    I had prepared a Attendance Sheet which is auto populated with P (present) till present date and A (Absent) if entered manually in another sheet named "Absent" with the following formula: =IF(E$2TODAY(),"",IFERROR(VLOOKUP($A4&"-"&E$2,Absent!$A:$E,5,0),"P")))).

    My query is What is the formula for making a series of A (Absent) if one of an employee is absent from a given start date to given End date in sheet "Absent". Also what is the integrated formula for an employee who had retired /resigned on a specified date

    Thanks for your support

  15. Hi,

    I am trying to use a formula to automatically calculate the length of time until the next meeting based off today's date. The dates of the monthly meetings will be stored in a separate worksheet. So far I believe something like the formula below should work?

    =MIN(IF('Schedule V.1.1'!D13:D22>=TODAY(),'Schedule V.1.1'!D13:D22,""))

    Any help would be greatly appreciated.

  16. Hello,
    I am trying to search for any date in three columns and add 2 years to the date it finds. any suggestions?

  17. Hi Im trying to populate a calendar from one date.
    eg if I enter the 20th of november this will create a number of dates and jobs based on formulas from the entered date
    eg 20.11 start date will mean on 20.11 + 83 job A needs done and 20.11 + 123 job B will need done
    Is this Possible?

  18. How do i get a formula to change a date (12/25/10) to a # (dpd)

  19. Good day,
    Can you maybe help. I have a spreadsheet with the age of issues (issue log). I need to split the ageing of the issues into 60 days to count how many of the issues in each of the ageing falls to present it in a pie chart at the end.

    How do I calculate the counts for each ageing categories?

    Thank you,
    Leana

    1. Sorry, I noticed the detail is incorrect - apologies.

      It must be split between 60 days.

      1. Sorry, it seems like it converts my calculation....

        It reads: It must be split between less than 30 days, 31-60 days and more than 60 days

        1. Hi Leana,

          Please provide more information on how your data is organized. In particular, how should the aging of issues be calculated? Do you have a column of issue dates that should be compared with today's date?

  20. Can you help me out with following problem.
    I want to calculate total delay time and the total early time in the attendence sheet.as a example,our office start at 7:00AM. if some one came at after that time or early that time,I want to calculate total delay times and early times for a month.

    1. please help me

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

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

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

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

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

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

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

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

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

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

    tried this one and not working as well is saying 0

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

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

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

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

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

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

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

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

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

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

  41. how to write date formula

    1. date formula ctrl+:
      time gormula ctrl"

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

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

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

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

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

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

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

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

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

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