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

  1. Hello,
    I am trying to figure this out and need some help.
    Here is planning challenge. I run operations all year (Oct 1st - Sept 31st) minus Nov, Dec, and Aug. Based on the current day I need to subtract the number of days in those operation day. For example, if today was Dec 12th, I will need to subtract 42 days (Nov 1st -Dec 12th) from current total 73 days (Oct 1st - Dec 12th). The answer I am looking would be 31 days. So, it would not matter what day of the year it would be , the math would subtract those non working days in Nov, Dec, and Aug. Any help would be greatly appreciated.

  2. I want a formula to compute for future date.
    A1- date today 12/12/19
    B2- number of dates its on the list example: 23 days on the list today 12/12/2019
    C3- fixed value of 120 days
    D3- i want to know what date will it be for the 120th day of the name on the list using the 3 variables.

    Thank you

  3. Hi,

    I want to make formula if employees work more than 5 years he entitlement for 30 days & if less than 5 years entitlement 15 days.

  4. trying to use the following =IF(B2<7/1/2020,"ok","update now").... what i am trying to do is, is the date in b2 is before 7/1/2020 i want it to output "update now" if the date is after 7/1/2020 i want the out put to be "ok"
    AC Tanks 6/18/2020 Sentry Insurance update now
    Baldwin GCPC 12/31/2019 Ala Homebuilders ok

  5. I have a table of almost 400 entries/rows. I need to subtract one column of dates (x/x/xxxx) from another column of dates (x/x/xxxx) and produce a third column of the results as whole numbers in days. I use the Business Edition of Ablebits. What are the keystrokes/buttons to do this? Thanks so much.

  6. Please which formular do I use to add a counter to a date. for example if on cell A3 is a date 12/09/2019. How do I add a counter to count the number of weeks on another cell B3. If the start date in A3 is 12/09/2019 then if I want to know how many weeks I have spent on the studies on 24/10/2019, then I want cell A8 to show 6 ( the weeks between the two days). Please help!!!!. Thanks

  7. HOW CAN I GIVE THE DATE OF NEXT 2DAYS SUPOOSE MY VALUE 30 SEPT 2019 SO I WANT NEXT COLLOMN AUTOMATIC ADD 2 OCT 2019 SO HOW I GIVE THE VALUE PLEASE TELL ME RESPONCE

  8. Fantastically helpful webpage!

  9. Hi team
    Please help, i have a spreadsheet with accumulating employee leave days. i want to a formula to automatically add 2 days to the accumulating leave column every end of each month.
    anyone please help!!

    Thank you

  10. Hellow ,
    Kindly I need help , I have finsied date and pendings days for example :
    finishing date 02/08/2018 and pendings date -367 my question how can I find start date?

  11. Junuanry 26 1976
    July 29 2019

  12. I am trying to calculate an employees years, months and days employed. They have 2 separate dates of employment that I want to add together.
    START DATE END DATE
    8/15/1999 7/1/2016 16YEARS 10MONTHS 16DAYS
    11/6/2017 present 1YEARS 7MONTHS 26DAYS

  13. Im trying to set up an excel formula so that if my dates are less than a year that the data would not be included in the sum.
    Ex today is june 14
    My data is:
    Date
    1/1/18. 3
    2/5/18. 4
    3/4/19. 6
    Total would be 6 (formula here)

  14. My spreadsheet calculates a patient visit schedule from a specific date I enter. I then import these dates into my outlook calendar. How do I create a formula for a specific date plus 14 days under "general" formatting - not the date formatting? The data with the "date formatting cells" do not import in outlook or google calendar.

  15. HI,
    I would like to get the difference in the dates "4/13/2019" and "2/25/2019" so used function "=DAYS("4/13/2019","2/25/2019")", but getting the result like this .."2/16/1900", i was expecting 47, but no matter which function(Tried with today(), simply subtraction) i am using, i am seeing the same result.. can some explain me why so?

    1. Always make sure to check the formatting of the cells. This can be fixed by changing the date format to a number.

  16. Hi all,

    I have a spreadsheet that has an issue date, importance rating and then a review date. What i'd like to happen is that if the importance rating is high then the review date needs to be the issue date plus 1 year, and then so on with all the other options but the one i have used isn't working and adding YEAR doesn't seem to be working either....

    Here is what i have:
    =IF([@Importance]="High",[@[Issue Date]]+1,IF([@Importance]="Medium",[@[Issue Date]]+3,IF([@Importance]="Low",[@[Issue Date]]+5,"N/A")))

    Any help would be great!! :)

  17. how I convert number 20 to 1 year 8 months
    also 38 to 3 year 2 months
    hope you can help..

  18. I need to calculate the total deal cost for deals with a start date and end date and an annual recurring rate. Leap years need to be taken into account. Deals are usually between 12 months and 48 months. I would like to see a result like 3.26 year where all years are calculated as if they are being 365 days. Hope you can help.

    1. DATE(YEAR([@[Contract Start]]),MONTH([@[Contract Start]])+12,DAY([@[Contract Start]])-1)

      1. Wim, I was solving for one year, less one day. This is the formula I was able to get to factor in leap year. I'm not able to delete my reply, so I'm not sure it applies to you.

  19. I want to subtract two dates like this- Tue Oct 23 21:44:03 BST 2018
    How can I do it please?

  20. I would like to calculate number of days from start date to end date, covering all days.
    for example; Travelling dates from 1 Mar to 5 Mar = 6 days
    what formula does suit to calculate?

    Thank you

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

  49. GREAT sharing. thanks so much

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

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