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

  1. Hello!

    I love your tutorials! I do have a question though.
    I have a documentation sheet that has "Move In" dates and "Move Out" dates for renters in their own separate columns. I also have a column that counts the days from the date that they first move in. For example Move In Date is 12/25/2016 so the current number of days in house according to today's date would equal 25. However the formula I am using currently continues counting from the move in date to the current date (today). How would I get my counter to stop counting based on the move out date and retain the counting ability?

  2. Hello! Could you help me with a formula to add (sum) two periods. For example if I wish to add 01 year, 06 months, 12 days with 02 years, 07 months, 26 days how can I do it? With what formula? The correct answer would be 04 years 02 months and 08 days. But what formula in Excel would give me that answer. Thank you.

  3. my dog show is on 28/1/17
    i want to calculate age from 4 months to 6 months, 7 months to 12 months, 13 months to 18 months, 19 months to 36 months from cut off date which is 28/1/17

  4. Hello,

    I have a start date and I have it so it tells me how many days open, but how would I get that to stop when I put in a date completed?

    Thanks

  5. I would like to calculate 18 months from each of the dates for an entire column?

  6. hi ,
    i would like to make a formula that automatic increase the value every year in April or if i change the cell to month April other cell automatic the value with the percentage increase

  7. Hi,

    How to find out the due date for one who completes the probation period i.e. 6 months. The due date should be in DD/MM/YY. Please help.

  8. AMAZING SITE! ALL ANSWERS ANSWERED SPECIFICALLY.i would like to create a calendar with events, but i would also like the events to be automatically written out in the calendar.
    is there any resources on your site that helps?
    thanks.

  9. Hello, I have used this site many times looking for solutions to my Excel problems. I am trying to figure out a way to add to dates in different columns. I am tracking things weekly beginning on Monday and ending on Sunday. Short of manually adjusting every cell, is there a formula to fix?
    Example:
    C1 is 1/1-1/8
    D1 is 1/9-1/15
    E1 is 1/16-1/22
    F1 is 1/23-1/29
    G1 is 1/30-2/5
    etc. every week to 12/31

  10. Hi,
    how may I convert a given sample/answer (example: 3d 10h 35min) in a certain cell into a total minutes only?

    Thank you.

  11. Hi,

    Need help please!

    I have a date in cell B2. I also have a a table from A5 to B8 (Column A are date values and Column B are price values).

    I need for Excel to use the date in B2 to search for the latest date in Column A before the date in B2, and throw me back the price from Column B.

    For example:

    B2=01/05/2017

    A5=01/01/2017
    A6=01/01/2017
    A7=01/03/2017
    A8=01/15/2017

    B5=$3.00
    B6=$3.00
    B7=$3.50
    B8=$3.70

    I need the formula to give me the $3.50 price.

    Can you please help me? I know it seems simple, but have been having a hard time with it.

    Sincere regards

  12. I'm ddoing a spreadsheet for my budget of the different bills I owe. My issue is that when I enter new dates for next year it adds 2016 instead of 2017. Can someone tell me how to fix this? I should be able to tpe in the month/date hit enter and the year auto populate but it's doing it for 2016.

    TIA

  13. Thank you for all your wonderful knowledge. Would you be able to help me do the following?

    I need to have a specific starting date such as 1/1/17. I need the formula to calculate 91 days into the future that is a Wednesday closest to the 91st day but not less than the 91 days. The starting date will change every time I need to make an entry, so the formula will calculate the best Wednesday.
    thank you,
    Brian

  14. I am trying to calculate the difference between starting and ending time on a work shift. The calculation is okay if the hours are all in the same day. i.e. start 8:00 am and quit at 5:00 pm.

    The problem I am having is if the starting time is 6:00 pm and the ending time is 3:00 am. I was hoping the answer would be 9 hours. Instead, the answer is 15 hours.

    The cells are formatted in hh:mm. The cells are not formatted like dd-mm-yyyy hh:mm because of the number of employees that need to be entered.

  15. Hello, I am a newbie at this and trying to wing it the best i can to get a jump start. I am trying essentially to calculate 30 day, 60 day, and 90 day increments based on a random start day to arrive at an end day for late fees. I have to of course consider 30 and 31 day months and february of course being a 28 day month this year. Holidays are not an issue. Can you help me figure out how to enter this formula in excell? Thank you!

  16. Sorry I can't find a solution to what I'm looking for in the large amounts of questions. Any help greatly appreciated:

    I'd like a formula that adds a certain value, or indeed a multiplier, based on a date of the month. i.e a cell that adds another €100 automatically every 25th of the month, even better if it can stop after a year (Jan-Dec)

    I'm aware there would be the very long IF function variant using TODAY and a bit of juggling, but there must be a neater formula?

    Would love to hear some ideas. Thanks!

    1. A botched workaround to my above question:

      =IF(TODAY()<DATE(2016,12,31),(ROUND(((TODAY()-DATE(2016,1,1))/30),0)*[desired value]),(12*[desired value]))

      Does the trick, just only comes into effect half way through the month and assumes 30 days in each month.

      Would love to hear how to nail it down to a specific date.

  17. What formula to use if you wants to know age calculation.
    E.g. born on 15th May 2010 and I want to auto calculate age to the date of 31st Dec 2020 ?
    Please help. Thank you

  18. I have a spreadsheet broken down by pay periods and want to apply a formula to change the range of week dates in each subsequent cell. Example: Pay period 1 in January 2017 is 8-14 for the first week and 15-21 for the second. I want a formula to deliver 22-28 in the next cell so I don't have to look at a calendar and manually enter the dates.

  19. Hi,

    What about payroll cut-off date to be published in a cell, say for example cut-off date is from January 1 to 15, 2016. Is there a formula to show this in a single excel cell? Thanks in advance.

  20. Hi,

    The answer to the above question (# 141) is
    =IF((AND(E2"",F2"")),(MIN(TODAY(),F2)-E2+1)/(F2-E2+1),"")

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

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

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

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

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

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

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

  28. Hi,

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

    Regards,
    Santosh

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

    tried this one and not working as well is saying 0

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