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

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

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

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

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

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

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

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

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

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

  10. I need to get the date difference in the this format (years, months and days). Can anyone help me??

  11. How can i subtract 2 Y 11 M 12 D from 39 y 1 M 30 D

  12. well explained indeed.
    Thanks a lot

  13. I'm attempting to create a conditional format that will cause a cell that has a future date in it, to be flagged yellow when that date is 90 days away.

  14. Hello,

    I am trying to subtract date/time but not getting accurate result. Please help me with it.

    7/15/2016 10:30
    7/16/2016 14:00

    How to find the result from above given date/time?

    Thanks,
    Ravi

  15. Supposing I have a ranges of cells comprising of due dates that fall on weekends, holidays and weekdays. Whenever a due dates fall on holidays or weekends, transactions would commence on the next following working day. I used IF, OR, WORKDAY, and WEEKDAY functions but i found it very difficult to suppress due date that falls on weekdays (not a holiday) from incrementing by one(1)day. I am formulating a formula that would address all of the records at the same time. Hoping for your assistance.

  16. I got a date 12 Feb in Excel column and I want to add the year so it can be 12/02/2016 ?

  17. Hi there...Hope you can help me with the following.

    I want to add a date to a cell. Then in a different cell i want it to work out 6 months from the date in the first cell. IF the date then goes pass our current date, i want it to show red or say expired.

    1. Hi Lorien,

      Supposing you add the first date to A1. Then you can enter the following formula, say in B1, to add 6 months to the first date:
      =EOMONTH(A1, 6)

      And then, you can create a conditional formatting rule with the below formula to highlight the date in B1 with the red color when it goes past the current date:
      =$B1<TODAY()

      For the detailed instructions, please see How to conditionally format dates and time in Excel.

  18. hi please help me, i used =IF(AG2<(TODAY()+2),"DELIVERY DATE",IF(AG2<(TODAY()-1),"DONE","ON GOING")) formula but the result "done" doesn,t work.

    if AG3 is 2 days before delivery date the result is "delivery date" if after delivery date the result is "done" when AG3 has no date the result is "no supplier delivery date" otherwise "on going"

  19. I am trying to subtract two dates to get a calculated time between the dates in MM:dd:hh:mm format to see how long it takes to resolve an ticket. Example:

    Start Date: 05/01/2016 13:24
    End Date: 05/17/2016 18:04

    Should end up with 00:16:04:40, but it comes out as 01:16:04:40, and I can't for the life of me figure out how to make that month be 00 and be more accurate.

    I have found several articles that use the ability to manipulate the month itself, but doesn't describe how to subtract two dates AND round down on the month to show the accurate time between the two dates. Any help here would save what hair I have left. Thanks.

    1. Hi Sandy,

      You can use DATEDIF to get the number of complete months between 2 dates, and then concatenate the results of several functions in 1 formula:

      =DATEDIF(A1,A2,"m") & ":" & DATEDIF(A1,A2,"d") & ":" & TEXT(TIME(HOUR(A2), MINUTE(A2), SECOND(A2)) - TIME(HOUR(A1), MINUTE(A1), SECOND(A1)), "hh:mm")

      Where A1 is the start date, and A2 is the end date.

      The drawback of this approach is that the result is a text string and you won't be able to use it in other calculations.

      1. Yes that's the problem. I do need to be able to add the results of this together to get a total time spent on a ticket. Any other ideas?

        1. Sorry, I have no other idea. To my best knowledge, none of the Excel functions or formats can display a zero month or day.

          As a workaround, you can use 1 column to present the time difference in an easy-to-understand way (DATEDIF formula). And in another column, calculate the time difference with a simple formula like =A2-A1, and display it in hours and minutes [hh]:mm. And then, use that second column to get the total time.

  20. Hi! Is it possible to do math with dates which are formatted 3.4.2016? So separated with dots in other words.

  21. I need to calculate no of appointments today basing on date from 1) Data Sheet to dash boadrd

    Prabhakar T Total Number of Meetings the day 3-Jul-16 from data sheet containing the date

  22. I am looking for a similar calculation as SElizabeth. What I need is a gestational age calculated from a specific due date on a specific date. All I can locate is a formula that gives a calculation based on todays date...which of course will change the resulting value each day. I need for the result to be a fixed value.

    The formula I have is: =FLOOR(279-(K2-TODAY()),7)/7&" weeks "&MOD(279-(K2-TODAY()),7)&" days"

    The K2 value is clients due date. When I try to substitute the TODAY value for a cell that contains the date I want this to calculate off of, I get an error.

    I don't understand excel at all...so I am stuck!

    Any ideas?

    Thanks

  23. I'm trying to develop a spreadsheet of pregnant patients, and I need a formula to calculate today's gestational age from due date. The output would be something like "35 2/7", meaning 35 weeks and 2 days gestation. Here's the Google Sheets formula that does not work in Excel:

    =ArrayFormula(if(A1:A,int((280-(A1:A-today()))/7)&ʺ ʺ&mod(280-(A1:A-today()),7)&ʺ/7ʺ,iferror(1/0)))

  24. Hi,

    I have added 12 months to a date in column E4, but if there is not a date to add to (E4 is blank) then the date shows 31/12/1900. How do I make the cell blank rather than 31/12/1900? I'm having trouble adding an IFBLANK function to the below formula. Thanks :)

    =EDATE(E4,12)

  25. subtract 5/13/2016 1:20:39 AM- 5/13/2016 12:00:00 AM
    to find out result in hh:mm:ss

  26. Great information, exactly what I was looking for!

  27. I want to calculate how many years is remaining or expired for the lifetime of equipment from the actual date.
    Thanks

    In service Life Calc end
    date Time date
    14-Feb-05 15 11/Feb/20
    14-Feb-05 15 11/Feb/20
    10-May-03 10 07/May/13
    10
    22-Jul-12 10 20/Jul/22
    10
    22-Jul-12 10 20/Jul/22
    10-May-03 15 06/May/18

  28. hi,

    Wednesday, June 01, 2016
    i want to split day and date so how to possible ?

    1. Hello Hiren,

      If your original values are dates, you can use the following formulas:

      To extract the day of the week: =TEXT(A2, "dddd")
      To extract the date: =TEXT(A2, "mmmm dd, yyyy")

      If your original values are text strings, then use these ones:

      To extract the day of the week: =LEFT(A2, FIND(",", A2)-1)
      To extract the date: =RIGHT(A2, LEN(A2)-FIND(",", A2)-1)

  29. Thanks Svetlana, im new to this!

  30. =IFERROR(MONTH(BA5),"") IS RETURNING 1

    1. Hi Dan,

      Just use IF instead of IFERROR:

      =IF(BA5="","", MONTH(BA5))

  31. If I have no date can I return a blank cell?

  32. Hi, I'm trying to organize a schedule for work.

    My sheet is set up so that the person creating the schedule (usually me) enters the Month in a cell, and the week number in another cell (Week number of the year, but could be of the month if it's simpler).

    Once these two fields are entered, I'd like a group of cells to be updated with the dates.

    For example:

    Week Number: 22
    Month: June

    30 31 1 2 3 4 5

    I haven't found any information on how to accomplish this.

  33. Hey. I have been trying to calculate the hours spent working on a task, excluding daily breaks of 1:30.
    I also do not want to count non working hours. The day starts at 09:00, a break from 13:00-14:00, finishing at 17:00. Some of the tasks may take three days and even span over a (non working) weekend. Cheers.

    START, FINISH , TIME_ON_TASK??
    05-Apr-16 9:00, 06-Apr-16 14:00 =??:?? hours worked on task.
    05-Apr-16 9:00, 05-Apr-16 17:00
    06-Apr-16 11:00, 06-Apr-16 13:00
    05-Apr-16 0:00, 05-Apr-16 0:00
    05-Apr-16 0:00, 05-Apr-16 0:00
    05-Apr-16 0:00, 05-Apr-16 0:00

  34. Hi,
    I am trying to create a formula that can take a date in cell A1 and add months from A2 and give me a date in A3.
    The problem is I need to be able to change A1 or A2 without changing the formula and I want to copy this to about 600 cells.
    Thanks in advance for your help.

  35. Hi

    Need help to get formula for calculating overall experience. Ex, my previous experience is 3.6 years. My current experience started from 06-June-2012(this exp is calculated using dated if). How to calculate the exact total years of experience in excel(previous number + current)?

    Thanks to help.

  36. Need some help please and thanks in advanced.

    if I have a date in a cell 08/10/2016 and in the cell to the left of it I like the this date less a month.
    eg from 08/10/2016 to now read 08/09/2016

    thanks again guy's

  37. What if I want to know the number of employees given the hired date and end of contract?

  38. I am trying to add up hours worked in a month.
    Cells are custom formatted: [hh]:mm
    I used the formula D5=Text(C5-B5, :hh:mm") to get the hours worked on a particular day.
    Then I use =sum(D5:D35) to get the month total but the result is 00:00.
    How do I get this column to add up
    Many thanks
    It's a very useful site.

  39. How do i get a a cell to show a specific day before a specified date.

    I need a cell to show the Thursday before any specified date.

    IE cell C2 needs to show the date for the Thursday before the date in cell D2

    Thanks
    Ash

    1. Thanks Samantha, that's not quite what I mean though.

      Say I have today's date (24/04/2016) in cell D2, I need cell C2 to tell me the date for Thursday last week (21/04/2016) but also if I have say tomorrow or Wednesdays date in D2 I still need it to return the previous Thursday (21/04/2016)

      Hope that makes sense

    2. suppose you have date in D2 then you can put the formula in C2
      =text(D2,"dddd")

      help-
      "d" - only date will come
      "dd"- only date will come
      "ddd"- Mon, Sun. wed
      "dddd" - Monday, Tuesday, saturday

  40. hi
    i need to add or subtract time using now() function and entered value in time format only
    4/19/2016 9:06 5:05:00 PM 42478.67
    when tried I am getting the above value please help

  41. Hi guys,

    Please advise on below

    how to modify the next formula formulation for the following:

    If= required date less than 5 weeks,"check","no check)

  42. how can I do in one excel box to show a week like
    Jan 01, 2016 to Jan 07, 2016
    Jan 08, 2016 to Jan 14, 2016
    and so on

    I can do manually but I want to do it in a formula.
    Thanks

  43. Joining Date last date Previous Experience Total Year Experience Grand Total Experience
    25-May-14 1-Jan-16 5 years 3 months 1 years, 7 months, 7 days
    7-Oct-14 31-Mar-16 4 years 2 months 1 years, 5 months, 24 days
    21-Jan-15 24-Mar-16 3 years 9 months 1 years, 2 months, 3 days

    please help how to add Previous Experience to Total year experience....

    1. Sorry, it's hard to think about ways of getting data without seeing your table. If the "Previous Experience" column has a string value, you can try the following:

      To get the number of years and months from Previous Experience you can use formulas like these:
      =LEFT(D3, SEARCH(" ", D3, 1)) - for years

      =MID(D3, SEARCH("years", D3, 1)+5, SEARCH("months", D3, 1) - (SEARCH("years", D3, 1)+ 5)) - for months

      If the "Total Year Experience" column is countable, add the results to the formula for this column.

  44. The formula "How to add / subtract months to date in Excel" have a gap with date 29

    If I add 1 month from 29/01/2016 to 29/01/2017, the month of February gone missing.

    I manually solve the question, but needed to go deeply. THANK YOU.

  45. I want to add X number of days to the date is cell A2, showing the resulting date in cell B2. However, if I use =A2+1 (for example) to add one day to the date in A2, but no date has yet been entered in A2, then the display in B2 is "VALUE". Is there a way to not have that display, leaving B2 blank until a date is entered in A2, whereupon B2 would display the date according to the formula?

  46. I need a formula to identify any help desk ticket created between today at 12:01am and a week ago at 12:01am.

    So, in essence, I need to create a formula that says this:
    (If Ticket_Opened_date is between (today at 12:01am - 7 days) and (today at 12:00am), "New", "Old").

    The trouble is the part about it being at 12:01am and making sure the formula is dynamic so I don't have to recreate it each week. Note: Please trust me when I say I can't do it via the standard date filter options (so don't even bother suggesting that).

    Can someone help?

    1. Hello, Ruthie.

      I think that the following formula should work for your task:

      =IF(AND(D17>(NOW() - HOUR(NOW())/24 - MINUTE(NOW())/24/60 + 12/24 -7),D17>(NOW() - HOUR(NOW())/24 - MINUTE(NOW())/24/60 + 12/24 -7)), "New", "Old")

  47. Just like the comment 3 above i need to calculate the differences between dates to get a format like 2yrs, 3months, 5days, 7hrs and 25min

    for example difference between 20/10/2014 9:34 and 13/12/2016 17:20.to get
    2years, 1month, 23days, 16hrs, 14min.

    1. Hello, Bola,

      Please try this formula:
      =IF(DATEDIF(A2,B2,"y")=0,"",DATEDIF(A2,B2,"y")&" years ")&IF(DATEDIF(A2,B2,"ym")=0,"",DATEDIF(A2,B2,"ym")&" months ")&IF(DATEDIF(A2,B2,"md")=0,"",DATEDIF(A2,B2,"md")&" days") &" " &TEXT(ABS(A2-B2), "h") & " hrs" &" " &TEXT(ABS(A2-B2), "mm:ss") & " min "

  48. Hi,
    I'm trying to create an If function that would evaluate a score, then based on that score value predict a future test date based off of the date the first test was taken. Here are two examples:

    1.) TESTED: 1/1/2015 SCORE:>70 NEXT TEST:12 months from first date(1/1/2016) 2.) TESTED: 1/1/2015 SCORE:<70 NEXT TEST:6 months from first date(7/1/2016) Do you have any idea how to format this correctly? Every time I try, I end up with either an error, or the result of my text. Thanks!

  49. Is there a way to calculate the number of years between four dates? I want to calculate years of employee service where there was a lapse in employment:
    Start date 8/22/2005, End date 8/13/2008 = x years PLUS Start date 7/1/2009, End date 1/19/2010 = TOTAL YEARS. I tried adding two dated ifs together but it didn't work EG: =DATEDIF(A1,A2,"Y")+(DATEDIF(B1,B2,"Y"). (Answer s/b 3 years) Thx!

    1. Hello, Barbara,

      DATEDIF(A1,A2,"Y") returns the number of complete years. In your case 2+0 is 2. To get the correct result you need to sum 2 intervals and count the number of complete years using DateDIf:
      =DATEDIF(0,(A2-A1+B2-B1),"Y")

  50. How can I subtract in excel for below issue?

    [d/m/y h:m AM/PM-d/m/y h:m AM/PM]and note that fri and sat will be excluded from the calculation

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