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

  1. Hi
    Both of below are not working, please help
    =DATEDIF(B2, TODAY(), "d")
    =TODAY()-B2

  2. Hi. I'm getting a #value! error message when I input this formula =DATE(YEAR(L25) - 1, MONTH(L25), DAY(L25)).

    L25=Aug 19 2022

    I'm simply trying to reduce the new date by a year. Why am I getting the error message?

    Thanks

  3. Is there any formula to add 5 years 7 months 10 days with 10 years 8 months 25 days in excel. Please help me

  4. Thank You!

  5. hi,
    need help for cell D1
    how do I add the following CUMLATIVELY by formula?

    A B C D
    1 from to difference cumulative
    2 01jan2017 24mar2017 0 y 2m 23d(used datif) 0 y 2m 23d
    3 24jul2017 12nov2017 0y 3m 19d(used datif) 0y 6m 12d (how to add to d2)

  6. I have specific date in one cell, from there i need to go 4 weeks forward. Use =Date function i have just calculated 4 weeks. but my question is, if those four weeks falls in december month in between , i want to add 7 (1week)more days extra due to shutdown holidays... how to enter this scenario in exisiting date range formula?

    existing formula - =DATE(YEAR(D39),MONTH(D39),DAY(D39)+28)
    my commit date D39 - 30/11/2021
    I got 28th dec as output... but
    my expected date - 4th jan 2022

  7. I HAVE SMALL QUERIES IN EXCEL, MY MANAGER GIVES ONE INVOICE DATE 03-MAR-2021 IN THE DATE OF INVOICE TO 20 DAYS BEFORE UPLOAD THE DOCUMENT. NOW HOW TO USE THE FORMULA

  8. Hi,
    is there a formula where by I subtract dates from a set date (meeting date) and can establish which date I need to request reports showing only days that are monday-friday?

    I.e.meeting date is 10/04/2020 and i need to request the reports 3 weeks before that date I would normally use 10/4/21 cell reference - 21 but this often gives me weekends or holidays

    please help

    1. Hello!
      If you have a five-day work week, then you can use the formula

      =WORKDAY.INTL(A1,-15,1)

      You can learn more about calculating weekdays in Excel in this article on our blog.

  9. Hi Team ,

    Can you please explain , how to calculate the year

    Example :-3.5 to 4.11= 8,4

    Can you pls share the formula

  10. I have a problem! I have a cell that returns a date as a result of a formula in that cell (say, A1). I now want to create a future date (1 year ahead) in another cell (say A2), based on the date in cell A1. I know the formula needed to return a date a year ahead, but because the date I am basing it on (in cell A1) is the result of a formula, it is returning me a date of 31/12/1900 in cell A2. How can I get Excel to realise that that A1 is a date without having to go through copying and pasting values in a different cell?
    I am desperate - please help :)

    1. Hello!
      Without seeing your data it is difficult to give you any advice. I am assuming the date in cell A1 is written as text. What formula did you get this value with? For me to be able to help you better, please describe your task in more detail.

  11. Is there a way to add two cells containing the text 0 Years 5 Months 1 Days to 1 Years 6 Months 8 Days?

    1. Hello!
      I’m sorry but your task is not entirely clear to me. Could you please describe it in more detail? Give an example of the source data. How do you want to add these cells? What result do you want to get? Thank you!

  12. I have a start date (which passed years ago) and a end date but need to work out how many months are left from today ?

    1. Hello Lynne!
      Use the DATEDIF function to calculate the difference between dates in months. For detailed instructions, see here.

  13. Why are there so many errors on this page?
    > date) + N years, MONTH(date), DAY(date))

    Where's the beginning of that line????

  14. Thanks Svetlana. You are great!

  15. Great work!
    Thank you

  16. Dear Team
    How to add 60 years to the birthday in separate column.
    ex: 2 may 1983 and next column should add 60 years and should show as 2 may 2043

  17. hello,
    I is there any formula calculate date difference in Quarter and half yearly without rest of decimal.

  18. hello,
    I is there any formula calculate date difference in Quarter and half yearly without rest of decimal. I tried as follows
    datedif(date1,date2,"m")/3 and datedif(date1,date2,"m")/6 but result show me decimal.please give me a solution.

  19. Hello,
    I am creating a calculation sheet, and want to change the date automatically when the system date = enter date then entered date should be increase by 1 year automatically. please help to solve this problem. i have tried out but not happening.

  20. Hello, I hope you can help. I have search a number of places & cannot find the resolution. What is excel formula for this scenario?
    If I have a date such as: 2/7/2020
    If I would like to calculate a range of dates, 90 day from the date above with a range of dates plus 7 days & minus 7 days, I want to set up a spreadsheet for patient follow-ups that tend to have a window of time such as mentioned.

    Thank you very much,
    Tommy

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

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

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

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

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

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

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

  28. Fantastically helpful webpage!

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

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

  31. Junuanry 26 1976
    July 29 2019

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

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

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

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

  36. 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!! :)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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