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

  1. Hi

    Can you pl help me to get the Excel formula for the below

    I want subtract 4 and half months (4.5) from today date

    1. Hi!
      You can only subtract days from the current date. 4.5 months is from 134 to 137 days, depending on which months it is.

      1. Thank you.. I have not checked your previous reply

  2. I want to Add 10 working day in every date i want to Input.. please help.. i dont much about excel

  3. Hi, I wanna ask how to create the formula for conditional format over google sheets where I want a date to change its color into red when it has reach 1 year time from that particular date written down in the cell.
    for example: todays date is 27/6/22
    when i insert date 31/1/22 over the cell, I want the cell to change into red color (box) when it reaches 1 year time (31/1/23).
    I will be putting down more dates according the my data, so i can apply to all of them under this conditional rule, and they will change color individually according to their time in 1 year.
    Thanks for your help in advance.

  4. Greetings,

    I need to create an date-alert that flags equipment for maintenance.

    (Ex: Last service date was 4/15/2021. Next service date is 4/15/2022. I want a cell to change colors when the equipment is within 2 weeks of the next service date.)

    Any ideas would be treasured!

    JP

  5. I am trying to write a formula for months in role; however, for month 1 I need it broken down by half or whole, then month 2 as a whole month, and month 3 whole etc. Formula needs to be a living formula to change by date pulled

    1. Hi!
      Sorry, but I don't understand your task. Could you describe it in more detail? What result do you want to get? Give an example of input data and expected results.

  6. hi,

    for example:

    I have a loan (6000), I wand to subtract (500) in the 27th of each month.
    automatically by the system date.

    thank you

      1. YES, Good idea but,
        when:
        A1=6000
        B1= the formula
        D1= today()

        and I used this formula in B1
        =IF(DAY(D1)=27,A1-500,A1)

        would it continue to subtract every month? because if it false it will put A1 value after the 27th

        1. Hi!
          The formula I sent to you was created based on the description you provided in your first request. I can't guess what result you want on the 28th and beyond.

  7. Hello,

    If I have a date range, for example, 5/5/2015 - 4/30/2020 and I'd like to calculate the number of months within that range, but since the range begins on 5/5/2015 and not 5/1/2015, I would like the result to include one decimal point to represent May.

    1. Hello!
      Use the DATEDIF function to count the number of full months between dates. If I understand your task correctly, try the following formula:

      =DATEDIF(A1,B1,"M")+(EOMONTH(A1,0)-A1)/30

  8. IF i have one sheet with all users with the work date in one cell and hours in other cell and i want to present summary of the working hours spend by each user within the period by days using with formulas and not PIVOT table how to do this?? Can you help me?

      1. Thank you a lot! Its works.

        I have another issue if you can help me.
        I have 2 sheets, one sheet have data for example the id, the user name, dates, hours, issues etc, and i want to the second sheet to identify with id which has issue and on which date.

        can you help me with this please?
        Thanks in advanced

        1. Hello!
          If you want to get a list of IDs with dates, then you need to use the FILTER function. You can find examples and instructions in this article.
          I hope it’ll be helpful. If something is still unclear, please feel free to ask.

          1. Very nice its =filter(filter(..)

            Thank you ;)

  9. Hello,
    I am trying to figure out the formula that calculates vacation time daily. In my excel spreadsheet, I have a column labeled 'Hours Taken', and I am trying to put a formula for each employee that calculates the hours taken each day if that makes sense?

    Thanks

    1. Hi!
      The information you provided is not enough to understand your case and give you any advice, sorry. Please describe your problem in more detail. Include an example of the source data and the result you want to get.

  10. Hi

    I am trying to calculate the day and time difference between a start date and time and a start finish date and time for eg, start date is 12 jan 2022 15:00 and finish time is 19 January 2022 06:00.

    However I can't seem to get the proper cell format to reflect day and hours in my formula cell. or get the correction function for formula to calculate and say the diff is 6 days 15 hours.

    please help

  11. How to add the hours and old Years “Should I try total first then Add then Count the months to add for the total amount for the amount “ Would help me out better now “ to get more pd more for the customer “

  12. Sir,
    Is there any possibility for subtracting /adding academic years..
    For Example.
    In A1 value is given as 2010-2011
    If there any formula to get 2011-2012 or 2009-2010.

    Thanks in Advance

  13. How to calculate 3 years back date next month 1st date.

    For example if date is 25/12/2021, 02/12/2021 if any date in month of Dec 2021 then date is 01/01/2019

    And any dates in month Jan 2022 then it's 02/01/2019

    Is there an easy way to calculate the 3 years back next month 1st date.

    Please help me.

    1. Hi!
      You may read how to subtract 3 years of the date in this article above.
      To determine the last day of the month, use the EOMONTH function.
      Then add 1 day.

      =EOMONTH(DATE(YEAR(A1)-3,MONTH(A1),DAY(A1)),0)+1

  14. I have one task need to complete it within 5 days.

    If task create date is 13/12/2021 (Monday)then we will complete it 17/12/2021 (Friday) or before.

    But problem is if task create date is 14/12/2021(Tuesday) & 15/12/2011(Wednesday) then deadline is 18th and 19th Dec Weekend.

    If deadline is on weekends then we will complete this task on Friday.

    How can I add excel formula on this?
    Could you help me on this

  15. how to deduct 27/04/2020 14.00 hrs ( dd.mm.yy and 24 hours format) from 01/05/2020 10.00 hrs

  16. I wants to set the date with condition:
    Examples:
    for 1st to 15th I want set it’s to 2nd next month.and for the date between 16 to the end of months set it to 2nd next next month.
    What is formula for that condition?

  17. I'm working in Google Sheets, so this may not have the capacity to do this. Let's say I have a date in cell A1 as 1/11/2021, I need Cell B2 to show a date that is 6 days from the date in cell A1. I know how to enter the formula, that's easy as =A1+6 and returns a date of 7/11/2021. If the cell in A1 is left blank, the formula in cell B2 shows up as 05/01/1900. Is there a way to have the formula in cell B2 remain blank until there is something entered in Cell A1? I can do a work around and make the text white in cell B2 so it doesn't show and then make it black to reveal the dates when I have entered what needs to be entered into Cell A1. That's probably a very clumsy way of doing it though.

    So my question put simply is how do you enter a date formula into the cell and have nothing showing until has data to return as result from in the cell before it?

    Thank you for considering my question.

      1. Conditions my last date like 8/12/2021 after 12 days date than 11 days date than 8 days date but without friday. Whice formula use for this. Pls replay.

  18. please how do you get decades from a large dataset and sort them in order?

  19. Hi, i need to sum days to a date but just in workdays.

  20. hi, i need to substract dates formula of machine break down hours
    i.e : machine breakdown started
    A1 : 1/1/2021 B1: 6:00 am

    job done C1:15/1/2021 D1: 4:00 PM

    with minimum 10 hours each day = 150 hours(15 days)

    can some one help me out on this formula..

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

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

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

  24. Thank You!

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

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

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

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

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

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

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

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

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

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

  34. Thanks Svetlana. You are great!

  35. Great work!
    Thank you

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

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

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

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

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

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

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

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

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

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

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

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

  48. Fantastically helpful webpage!

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

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

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