Comments on: How to add and subtract dates, days, weeks, months and years in Excel

In this tutorial, you will find a variety of useful formulas to add and subtract dates in Excel, such as subtracting two dates, adding days, months and years to a date, and more. Continue reading →

Comments page 5. Total comments: 300

  1. Is it possible to change the colour of a box when a date is reached ?
    This is to keep track of machine servicing, I want to enter a date when the service was done and the box to show green until a year has elapsed when the box would change to red.

  2. Thanks a lot! I was searching a formula to add months in a particular date. I could find the right formula here.

  3. I want to subtract and calculate difference hours these two date
    Oct 14, 2018, 2:00 PM - Nov 1, 2018, 2:20 PM

  4. I am looking to have one column have the name of the months, i.e., "January." What I would like in column two is to have column one minus 3 months. So in the example January in column one, column two would show October (January minus three months).

  5. Hi there,
    I am setting up my class assignment schedule in excel but I have difficulty to setup a formula that will reduce the date till the deadline is reached. Please help me on this.

  6. Hi there,
    I am setting up my class assignment schedule in excel but I have difficulty to setup a formula that will reduce the date till the deadline is reached. Please help me on this.

  7. hi there, how can i make certain words fall automatically under a category.
    for example i have spoon, fork, knife, plate, bowl, saucer, i want the first 3 to always specify cutlery in their next column and the last 3 to always specify dishes in their next column any time they are typed the next column should automatically fill the category they belong.

  8. I need to know the no. of days between dates (with 1 days equivalent to 8 hours) and hours between time and combining these 2 to give me the total no. of days and hours in one cell.

    Given: 7/18/2018 - 7/20/2018 = no. of days
    8:00:00 - 12:00:00 = no. of hours
    Result should be in no. of days and hours (in one cell)

    Please help.

  9. I need to know the no. of days between dates (with 1 days equivalent to 8 hours) and hours between time and combining these 2 to give me the total no. of days and hours in one cell.

    Given: 7/18/2018 - 7/20/2018 = no. of days
    8:00:00 - 12:00:00 = no. of hours
    Result should be in no. of days and hours (in one cell)

    Please help.

  10. I have the following in cells A1 (start date and time) and B1 (finish date and time)
    A1: 29/12/2017 11:44
    B1: 02/01/2018 08:00

    What formula can I use to get the total time from A1 to B1 in HH:mm?

    1. I found this to work though giving decimal values
      =DAYS(B1,A1)*24+HOUR(B1-A1)+MINUTE(B1-A1)/60

  11. Hi, I'm looking for a formula to calculate when a payment is due when it's 30 days after month end.
    Eg.. Invoice date 15th May,the due date would be 30 days after month end..
    So I trust the actual date would be 30th June.. Is there a formula for this.?

    1. Karen:
      The formula you're looking for is:
      =EOMONTH(A22,1)
      Where 5/15/18 is in A22 and is formatted as Date as is the cell containing the formula.

  12. Hello. I have created a spreadsheet to record calculations for when to reorder tablet medication. The spreadsheet opens with Todays date but doesnt automatically look at the last stock count date and reflect actually how many tablets are left in the box......for me to reorder.

    I would like the formula for todays date cell to delete the stock count date cell and then delete the quantity of tablets number cell already recorded please.

    Thanks

  13. very helpful, didnt find it in excel help, thanks alot

  14. Kindly help me in sorting out this issue I want to calculate number of days between eg.29/11/2017 to 09/11/2017

    1. Just do latest date - earliest date
      e.g. Cell A1 contains 29/11/2017, Cell B2 contains 09/11/2017
      Do =A1-B2 the result will be the number of days (ensure the result is in 'General' format)

  15. Is there an easy way to calculate the number of month's, weeks, hours & minutes from a date to a date? This will cover leap years & any other variances I haven't already though of?

    TIA

  16. What formula should I use for the given scenario:

    Date on Cell 2 should be either 16th of the next month or 1st of the 2nd next month.

    1st to 15th of the month = 16th of the next month
    16th to 31st of the month = 1st of the 2nd next month

    For example, date in cell 1 is january 10, 2017, the date on cell 2 should be feb 16, 2017. If the date on cell 1 is january 26, 2017, the date on cell 2 should be march 1, 2017.

    Thank you.

  17. I have a formula that needs to be performed on dates every month that are larger than a date I have listed by a number, such as every 8th (8)or 22nd (22), and this is preformed again for each new month. My problem is having excel distinguish between a today() format and my number. Any recommendations?

    1. Hello,
      For me to understand the problem better, please send me a small sample workbook with your source data and the result you expect to get to support@ablebits.com. Please don't worry if you have confidential information there, we never disclose the data we get from our customers and delete it as soon as the problem is resolved.
      Please also don't forget to include the link to this comment into your email.
      I'll look into your task and try to help.

  18. When I tried formula =datedif(Cell2,cell3,"d") it displays result as #value!

    Kindly help me in sorting out this issue I want to calculate number of days between eg.18/11/2017 to 1/1/2018

  19. Do you have any formula to increment year of date by some no. like EDATE for month ?

  20. I have an Excel Spreadsheet that I am using to track success completing multiple tasks by companies. I have built a spreadsheet with a "Date Due" and a "Date Completed" column for each task. I already know the due dates and have populated the same. The dates items are completed are entered when done. I then calculate the difference between the Date Due and the Date Completed. I have built the simple formula of =Sum(D4-E4). I built the spreadsheet and copied the formula to all columns/cells that will eventually be populated. Here is my question, how do I get the cells that include the formula to return a 0 or nil value if the E4 value in the formula above is not populated? This means my work is still in progress and I don't have a "Date Completed" value. I am assuming an IF statement will do it, but I would like some guidance/recommendation . . .

    Thanks,

    1. Hello, Joanne,

      Please try the following formula:

      =IF(ISBLANK(E4),"0",E4)

      Hope it will help you.

  21. I have calculated difference between two dates using datedif formula
    but Iam having problem calculating difference from the result that i got from above formula like eg. (dd/mm/yyyy)

    Start date = 20/04/1983 End date = 31/03/2017
    the result i got is 33year, 11months, 11days. Now I want to substract 49 days from the result which Iam not able to do it. Please help me solve the calculation.

    1. Hello,

      Please try the following formula:

      =DATEDIF("20/04/1983",("31/03/2017"-49),"Y") & "years, " & DATEDIF("20/04/1983",("31/03/2017"-49),"YM") & "months, " & DATEDIF("20/04/1983",("31/03/2017"-49),"MD") & "days"

      Hope it will help you.

  22. I want to generate weekly report on sunday or on Monday, but data will be taken upto every Saturday, how can I use formula to calculate the values based on selected dates, e.g I have data from 1-aug-17 to 21-Oct-17, I want to calculate the values between these dates, but in next week how I can calculate it, as the date will be changed to 28-Oct-17 and so on....

  23. Very helpful. I used the subtract dates instructions for the simple task of calculating a series of due dates based on an upcoming meeting date. I didn't realize it was so simple, but I would not have thought of trying it without your instructions. Thanks!

  24. I'm having trouble listing dates via function.
    what i'm trying to get is:
    A1= Current Date in YY Mmm format {=TEXT(TODAY(),"YY Mmm")} lets say it reads: "17 Oct"
    A2= One month later: "17 Nov"
    A3= One Month after that: "17 Dec"
    and here is where things get to be an issue A4= One more month: "18 Jan"
    Which is how it should read, but the next one, A5, reads "17 Feb" when it should read "18 Feb"
    and then it should continue from there, updating the year as each January passes. And every time the current month changes, the months along the entire A column would reflect that.

  25. I am looking to show only the last 4 weeks of play for each player with a rolling total. I have tried a number of formulas and I am still having problems: (SUMIF and how to subtract weeks).
    The Total of 149 is for 5 weeks. 20 June 17 and 21 should be hiding or disappear and be replace by 27 June 17and 33 and so on. The total should read 128.

    W1 W2 W3 W4 W5 Tot

    20-Jun-17 27-Jun-17 04-Jul-17 11-Jul-17 18-Jul-17

    21 33 33 30 32 149

    Thanks for your guidance. Using Excel 2007.

  26. How to add 3years 4 months 5 days to 2years 4 months 5 days
    To find out total experience

  27. Hi ,

    The tab is called Open Issue -It has a reported date column ,
    I have a status column and
    What I need is the status column should display 'Less than 30day open' , 'between 30 to 60days' 'open and more than 90 days'.

    I was able to get =TODAY()-A5 = to get the number days open but not sure how to move forward from there

  28. Hi

    I enter multiple dates in Single Column and Deduct from Today's Date

    Rcpt Date
    25.7.17
    27.7.17

    Today's Date

    Now I want to Deduct today()-27.07.17

    Is it Possible

  29. GREAT sharing. thanks so much

  30. I have a column formatted as date YYYY-MM-DD, and I am using your formula to add 50 to the year.
    I used
    =DATE(YEAR(E2)+50,MONTH(E2),DAY(E2))
    I thought this would be simple, but I am getting a Value error. I am obviously missing something simple, but I am having one of those days

    Any help would be appreciated. Thanks

    1. I discovered the problem, Excel thinks the world started after 1900. My dates were 1800s and Excel does not recognize anything before 1900 as a date.

  31. How would I go about adding 90 days to a date. I am looking to add 90 days from one column to another.

  32. difference bitwen two dates one day is left plz solve this prob

  33. Hey!
    I'm trying to use dates for my study spreadsheet, and I have the end of semester date - Todays date which gives me my total days left. This is great, except I now want to subtract every thurs and fri (for work) from that! And I want this to constantly self update, so that when I pass those days, that subtraction is no longer reflected in the total. I also then want to subtract a specific two weeks (date to date)also for a trip in between.

    Please help :)

  34. Trying to estimate approximate production dates from the following data:
    1st Unit, serial 1701, produced on 01/July/1931
    165th Unit, serial 1866, produced on 01/July/1935
    Average .793 unit made per week

    How can I calculate the rough production date in DD/MM/YYYY format for each of the 165 units?

    Formula to be copied/pasted in existing excel sheet.

    Thanks in advance!!!

    Patrick

  35. how to add dates on excel?. I created a table containing data of daily sales with a date above on it but i can't filter each date since it never appears in filter

  36. Hi I am trying to calculate the difference between two numbers represented as years and months. ie What is the difference between 7 years and 2 months and 5 years and 10 months?

  37. hELLO,
    How to calculate the how many days left?
    If my campaign start Date 4/29/2017 and ends at 11/14/2017.

    How can i calculate that how many days left to ends this campaign?

    where Strat Date on cell B2 and Ends Date At cell D2 i have to calculate E2

  38. I NEED TO SHOW CORRECT MONTHS TO DETERMINE TAX EG. START DATE 1/APR/17 END DATE IS 31/MAR/18, IF I HAVE A DATE 4/June/18 or 5/Nov/18 how can i have to arrive at 12 months from start date. what formula I have to use to get correct months.

  39. i have calculated the experience of employees like form 1/1/1998 to 25/9/2000
    answer is 2 years, 0 months, 24 days and second form 1/11/2000 to 31-12-2002 answer is 2 years, 0 months, 30 days and third form 5/4/2003 to 08-01-2007 answer is 3 years, 0 months, 04 days now i want to calculate the three answers in a manner to 07 years, 01 month, 28 days.

    How could i am able to calculate it with the help of formula

    thanks

    Best Regards

    Shahid Zaheer Meo

  40. Thank you for a very interesting article and website.

    If I put 3/31/2017 in A1, the formula =DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))in B1, and the formula =EDATE(A1,1)in C1, I get 5/1/2017 for B1 and 4/30/2017 for C1.

    I was expecting the same result for each formula.

    Why is there a difference when both formulas are supposed to yield the same, namely one month from 3/31/2017?

    What am I missing?

    1. Hi, James,
      it is a very interesting notice! DATE and EDATE functions treat the months in slightly different ways. EDATE adjusts the results by adding a whole month (but since April consists of 30 days, its last day is shown). DATE, in its turn, adds the same number of days, but the 31st day is treated by the function as the 1st day of the next month. Please, look at the example below to get a clear understanding.

      DATE and EDATE functions results

  41. Hi I need a formula to know the number of days a project is delayed
    Ex.
    No. of day required days = 7 days
    Start date = March 20, 2017
    End date = March 24, 2017

    What formula can I use to reflect no. of days a project is advanced or delayed. Excluding Sat Sun and holidays

    1. Hi Ron,

      Let's say that start day is in A1, end date is in B1.
      First of all, you need to count the number of working days between your dates in C1:
      =NETWORKDAYS(A1,B1)
      You can find out how to exclude weekends and holidays here.

      Then, depending on how you want the result to return:
      a) if as a text in D1, enter the next formula there:
      =IF(C1<=7,"Advanced","Delayed")

      b) if you want to fill C with a colour, create formatting rules using formulas:
      =AND($C1<=7,$C1<>"") (green colour for advanced)
      and
      =AND($C1>7,$C1<>"") (red, meaning delayed)
      Apply the formatting for =$C:$C column.

      Hope it helps!

  42. I need a formula to calculate a date with a number if that number is greater than 0.

    4/27/2016 | 73 | __________
    7/14/16 | 0 | __________

    I tried using SUMIF but no luck. Is this possible?

  43. 07-Mar-2017 2:29:45 PM
    if you possible to add one cell

  44. Well after googling several add month solutions it appears that the 2 solutions actually do not work as I would expect them.

    How do you actually get the last day of the month based on the last day of the previous month ?

    If I add 1 month for example to the last day of September 2015 i.e:
    A1=date(2015,09,30) := 2015-09-30
    A2=date(year(A1),month(A1)+1,day(A1)) := 2015-10-30
    This is 1 day before the last day of October 2015.
    So it isn't adding 1 month but 1 month minus 1 day.

    I was expecting to get as a result: 2015-10-31

    Same result using EDATE
    A1=date(2015,09,30) := 2015-09-30
    A2=EDATE(A1,1) := 2015-10-30

    The only way I found to get this to work i.e: get the date of the last day of the month based on previous last day of the month is:

    A1=date(2015,09,30) := 2015-09-30
    A2=DATE(YEAR(A1+1),MONTH(A1+1)+1,DAY(A1+1))-1 := 2015-10-31

    1. The EOMONTH formula will return the last day of the month, and you can specify how many months before or after your original date. e.g. if your original date is in cell A1, =EOMONTH(A1,3) will return the last day of the month three months later. I hope that helps.

  45. Hi,

    I have a list of dates (column I) which show when training was completed. In column J I have my formula for adding 1 year to the date in column I for when the training is to be renewed. In some of column I i don't yet have a date, in column J this comes up as 31-Dec-00 as there is no data inputted in column I. Can i add something to my formula to say if the cell in column I is empty then for the cell in column J to stay blank?

    The formula i'm using in column J is: =DATE(YEAR(I2)+1,MONTH(I2),DAY(I2))

    Thanks
    Lauren

  46. I want to subtract number of year from Date 01-Nov-17 and 34 year

  47. Hi!

    I'm using the following formula: =DATEDIF(C6,D6,"d") to calculate the difference between days. Works well. However, when the date is the same in both cells, is there a way for me to add "1" to show a calculated answer of 1 in the final field? (Trying to account for number of days traveled in a month and, although it's the same day, it should still count as 1 day.)

    Thanks!

    Andi

  48. Hi,
    How do I get the below in excel,
    suppose I have a date in A1 as 01/25/2017 13:00 and in B1 I have time to be added as 56:15 (56 hours and 15 mins), i have to add these hours and mins to A1 and get the result as 01/27/2016 21:15

    1. I want to add hours to date & time

  49. how to do a programed yearly calaendar?

  50. Dear all,

    Hope you can help me with a formula in Excel 2010.

    If K:K is the letter "S" it must add 7 days to the date entered in I:I

    For Example
    S = 21-Jan-17 + 7 days > Entered in L:L as 28-Jan-17

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