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 6. Total comments: 300

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

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

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

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

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

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

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

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

  9. GREAT sharing. thanks so much

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  29. how to do a programed yearly calaendar?

  30. 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. :(

  31. 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 18-Jan-17

    Please help me. :(

  32. Hi,

    I need to add the number delivery days to an part that to find wne it will be return to us.
    For expample: A part was sent to supplier in January, return time from supplier is 6 month to return ie 180 days. This means I will have the part in warehouse in July.

    Can I have a formula for this... i have 1000+ part with different return date.

    Thanks.
    Riya

  33. I am using the formula
    =I2-TODAY()

    That gives me a number value, but I cannot get it to change to date.

  34. That is not what I'm asking. I have a spreadsheet with hire date, 3 month eval, 6 month eval, and 9 month eval. I want to take today's date and subtract it from the due date. If it is more than 5 days, I want the cell to turn red. If it is 5 days or less, I want the cell to turn yellow. Anything over 5 days will not change.

  35. In gregorian date if we mention date as 1/13/15 then it can show as 13.01.2015 and while applying Filter it will show us years 2015 2014 2013, by selecting the year we will have months then dates... same way I wish to have for Hijri (Arabic) dates as by typing 2/20/38 it shall show as 20.02.1438 and by applying Filter it should show 1438 1437 1436, then by selecting year it should show months then dates.... Please advise

  36. I want to take hire date and do evaluation at 3 months, 6 months and 9 months. I entered the hire date in b2 and want the values in c2, e2, and g2. I used the formula of 10/5/2016 as date in b2 with =sum($b2)+90)) to get the 30 day,=sum($b2)+180))for 6 months, =sum($b2)+270))for 9 months.

    Now in column d2, f2 and h2, I want it to highlight the cell red if date in c2 is more than days overdue and yellow if date is between 1 and 5 days until due. If evaluation is done or if evaluation is more than week from due, cell remains white background.

    How do I get it to subtract the due date from today's date and set the cell to change colors?

  37. Hi, how do I subtract 3 months from an end date given and the result must be the month end date instead beginning of the month?

    For example:
    3 months prior to due date Due date
    31/10/2017(I must get this result) 31-Jan-18
    01/10/2017(this result must be 30/09/17) 31-Dec-17
    30/08/2017(this result must be 31/08/17) 30-Nov-17
    (Formula =DATE(YEAR(M36), MONTH(M36) - 3, DAY(M36))

  38. Pls tel me the formula to get

    06-01-17
    06-02-17
    06-03-17

  39. Hello, I need help with a simple understanding manual formula because i can not use excel to get the answer to the following: 3 years 5 months 40 days + 4 years 8 months 15 days - 10 years 15 months 40 days

    How would i get the answer using paper?

    Thanks for your help and would greatly appreciative!

  40. Is there a way to have a date in A1 through E1 but then in F1 through H1 have the date be a week later? Basically I want a pattern of every 5 columns to have a date and the following 4 column have a week later and continue that 5,4 pattern?

  41. I need urgent help ..

    i need to add (N) number of days to costume date ( DD/MM/YYYY) with excluding Friday and calculate Thursday as half day .

    Meaning :
    operation take 23 days
    today date : 1/1/2017
    Friday is off and Thursday half day working ..

    i want to know the date when operation end .

    any help

  42. Hello,

    I am trying to calculate a result in where I can determine an expiration date of a specific date within 1 month.

    Example- Start Date is 2/17/2016 and End date is 02/16/2017. I would like to create a formula that will be able to tell me when the end date expires
    1 month before.

    Is that possible and how can I use the Edate and Today formula to get that calculation?

    Thank you in advance for your help!

    Melissa I

  43. Hi i am trying to work out days elapsed on a task

    Start Date - Completed date (this is easy) but if the item is not closed and no date is entered i want Todays date to be default

    Please help

  44. I can not for the life of me figure out how to do this!
    I want to track how long staff spend on task.
    They put the date & time started and the date & time they finish.
    I need to get the total hours & minuted (hh:mm)working.
    Deduct :30 for break and 1:00 for lunch. Then if the start and finish is greater than a day, subtract the 15 hours (overnight)

    H1[START] = 24/11/2016 09:45
    I1[FINISH] = 25/11/2016 08:35
    J1=[TIMEWORKED] ??? (hh:mm)

    Work day is from 06:00 - 15:00
    Break from 09:00 - 09:30
    Lunch from 11:00 - 12:00

    I need to do this macro free. I have been trying to sold this for ages. Looked all over the internet. What I have established is that I need to show the times as general formatting do the calculations and then display the result as hh:mm. My head aches trying to resolve the calculations though.

    Any help, ideas, advice??
    Cheers
    Steve

  45. Hi all,

    Following this thread and trying to create a list in Column A to reflect a weekly schedule with the value in A3 being the baseline and incrementing A4, A5, etc... by 7 days, simple stuff based on this site... so I thought... ;)

    Desired results
    Cell A3 - 1/10/2017
    Cell A4 - 1/17/2017
    Cell A5 - 1/24/2017
    etc...

    Current configuration
    Cell A3
    - Format: Date (3-14-2012 from Date format Type list)
    - Current value: 1/10/2017

    Cell K1
    - Format: Number
    - Current value: 7

    Cell A4
    - Format: Date (3-14-2012 from Date format Type list)
    - Formula: =DATE(YEAR(A3),MONTH(A3),DAY(A3)+$K$1)
    - Resultant value: #VALUE!

    Investigation
    I'm on Office2016 64-bit on a WINDOWS7 64-bit, i7 CPU/16GB RAM

    If I go to Cell A3 and "drag down" to A4 the date increments to 1/10/2018 so it seems as though it is recognized as a date...

    Looking at the Function bar dialog box when you click on "fx" it shows:

    /snip

    DATE
    Year: YEAR(A3) = #VALUE!
    Month: MONTH(A3) = #VALUE!
    Day: DAY(A3)+$K$1 = #VALUE!

    Formula result =

    /snip

    I then look at "Evaluate formula" and get (CY2017 is the tab name):

    /snip

    Reference Evaluation
    'CY2017'!$A$4 = DATE(YEAR("1-10-2017"),MONTH(A3),DAY(A3)+$K$1)

    The next evaluation will result in an error.

    /snip

    No change if I modify Date "type" format or change "/" to "-"...

    Questions
    Why would the date not format to 1-10-2016 per the selected Date format?
    - I went to a blank cell and modified the cell format to the same as above, when I then enter 1/10/17 it changes to 2001-10-17... huh??

    Appreciate any insight, thanks.

    --
    Dave

    1. OK, all I can say is WOW... :) I got this to work but it seems to me to have been MUCH more complicated than need be...

      Additional/changed config (from previous post)
      Cell J1
      - Format: Date (*2012-03-14)
      - Current value: 2017-01-10

      Cell A3
      - Format: Date (3-14-2012)
      - Formula: =DATE(YEAR(J1),MONTH(J1),DAY(J1)+0)
      - Resultant value: 1-10-2017

      Cell A4
      - Format: Date (3-14-2012)
      - Formula: =DATE(YEAR(A3),MONTH(A3,DAY(A3)+$K$1)
      - Resultant value: 1-17-2017

      I then drag A4 down to fill out the rest of the year and it works.

      Really feels like this was way more complicated than need be, thoughts?

      --
      Dave

  46. HI,,,
    Hope you doing well in the best of health. I really like the page, which is much helpfull. I need help please guide me.
    I am doing job at college and we deal majority student matters.
    if a student deposit his/her fee after due date, and due date is e.g 25-09-2016 and deposit date is 27-10-2016. each day after due date will b charge as fine Rs. 100. how i will do it with formula........please help

    1. Hi Naveed,

      Assuming the due date is in A2, and the actual payment date in B2, and the fine is 100 per day, you can calculate the fine amount with this formula:

      =IF(B2>A2, (B2-A2)*100, 0)

      If necessary, you can add the fine to the original amount, which is, say, in cell C2:

      =IF(B2>A2, (B2-A2)*100+C2, C2)

  47. I have a date derived (via formula) in a cell say A2 and I want to have a new date in cell B2 by adding 2 to Cell A2.

    best part.. In one excel sheet I am able to derive it, but on another sheet I am not :(

    Not sure whats the issue. Please help.

    Thanks,
    Manish

  48. how to do formula to deduct

    A.) (7 yrs, 7 months) - (6 yrs, 5 months) = Answer (formula??)
    B.) (8 yrs, 2 months) - (9 yrs, 1 months) = Answer (formula??)

    formula for

    A.) the difference between three nos.

    compare 1st assessment to 2nd assessment
    compare 2nd assessment to 3rd assessment

    example.

    1st assessment result is 20
    2nd assessment result is 18
    3rd assessment result is 25

    many thanks in advance for your help!!!

  49. i m using a formula in excel =IF(DATEDIF(E7,H7,"D")>0,DATEDIF(E7,H7,"D"),"expired")

    in this i want to add +1 to day in DATEDIF(E7,H7,"D") (i.e) i want the day next to the calculated date . it is possible? if so how , thanks in advance

  50. I need to get the date difference in the this format (years, months and days). Can anyone 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 :)