Calculating Differences Between Dates

To calculate the difference between dates:

Use the DATEDIF function. The results of the calculation are displayed as days, full months, and full years.

Note: The DATEDIF function is not included in the Insert Function dialog box.

To know more about DATEDIF function click on the following links:

 

https://www.exceltip.com/tips/how-to-calculate-days-in-excel.html

https://www.exceltip.com/excel-dates/calculate-number-of-minutes-between-dates-time-in-microsoft-excel.html

https://www.exceltip.com/excel-date-time-formulas/calculating-number-of-days-weeks-months-and-years-between-dates.html

Users are saying about us...

  1. I need to be able to calculate the number of working days between two dates where Saturday is a scheduled working day making a 6 day week. Lotus 123 made this a pretty simple matter, but I’m stumped with Excel. I think Microsoft dropped the ball. Can anyone please help?

  2. i need to calculate the dates through cells such tt the cells will b displaying the different dates. And the other cell would b calculating the date diff between these to dates, such tt the dates can entered dynamically instead of typing it down manually

  3. “I need to be able to calculate the number of working days (i.e. Monday through Friday) between two dates given where Saturday and Sunday are not counted as working days making a 5-day week.

    The two dates are: The first date is a TEST STARTING DATE and the second date being TEST COMPLETED DATE.

    Thank you very much.”

  4. the system run the report on will only produce the dates in the format YYYYMMDD it will convert the layout to EXCEL however the dates remain in that format – the end result that i seek is to determine when those dates are past due – I think the key is to be able to convert the dates so that they can become part of what would be a simple formula – how do I convert these columns of dates – I appreciate your help

  5. “Is there a function in Excel which allows you to forecast a date, i.e. what will the date be 3 years from todays date, for example? Can this only be done by years or can it extend to include months and days perhaps?
    Appreciate your advice in advance.”

  6. “Hi Everyone,
    I have come up with a solution to forecast dates.
    You have to use the numerical versions of the dates, i.e. by highlighting a date and pressing Ctrl+Shift+#, (to get back is just Ctrl+#), then you add 365 days times by how ever many years you want to forecast. Then convert back to date format!!

    This is quite long winded, does anyone know an easier way of doing this?

    Kind regards”

  7. A slight problem Arun: Every 4 years it will be off one day because of leap year. Maybe + 365 1/4 per year? Just a thought. Is there any way to create a calendar in excel that shows a set number of months from each year and rotates them according to the current date? (ie. if the current month is March it would show Jan. Feb. Mar. Apr. May Jun. , then a month later, when it is April, it would show Feb. Mar. Apr. May Jun. Jul.) Any help is greatly appreciated. Thank you.

  8. “DATE(YEAR($B$2),MONTH($B$2),DAY($B$2)-30)

    this is the formula that works for me to calculate a date minus 30 days. B2 represents a cell that contains today’s date. You could add or subtract values from the year/month/or day.”

  9. “Hi Guys,

    I strongly suspect I am missing the point here, but getting a date that is, say, 30 days prior to another date, is simply a matter of subtracting 30 from the date value.

    For example:

    A1 = 37846 = 13 August 2003

    A2 = A1-30 = 37816 = 14 July 2003

    What am I missing?

    Alan”

  10. “How can I calculate the number of days between a range of date cells. ie. cells A7-A49 are formatted as dates. When filling in these dates, I would like cell A50 to display the total number of days, and automatically change when a new date is added. Any help would be appreciated.

    Thanks “

  11. “Hi Mark,

    I’m not really sure what you are asking for, but if A7:A49 contains dates, then the following formula will give you the number of days between the earliest and the latest two dates contained within that range:

    =MAX(A7:A49)-MIN(A7:A49)

    If that is not what you are looking for, please can you expand a little, and give a short example, with the results that you expect.

    Alan.”

  12. “Alan,

    =MAX(A7:A49)-MIN(A7:A49) Worked perfectly !!
    Thanks very much for your quick response.
    Your help is very appreciated.

    Mark “

  13. I am working with dates before 1900. Using them in a calculation for example (each of these in a seperate cell) 1/7/1865 then 12/6/1865 wanting the difference. I can’t get excel 97 or 2000 to recognize these as dates.

  14. I need to be able to calculate the number of working days between two dates where Saturday is a scheduled working day making a 6 day week. Lotus 123 made this a pretty simple matter, but I’m stumped with Excel. I think Microsoft dropped the ball. Can anyone please help?

  15. i need to calculate the dates through cells such tt the cells will b displaying the different dates. And the other cell would b calculating the date diff between these to dates, such tt the dates can entered dynamically instead of typing it down manually

  16. the system run the report on will only produce the dates in the format YYYYMMDD it will convert the layout to EXCEL however the dates remain in that format – the end result that i seek is to determine when those dates are past due – I think the key is to be able to convert the dates so that they can become part of what would be a simple formula – how do I convert these columns of dates – I appreciate your help

  17. “Is there a function in Excel which allows you to forecast a date, i.e. what will the date be 3 years from todays date, for example? Can this only be done by years or can it extend to include months and days perhaps?
    Appreciate your advice in advance.”

  18. “Alan,

    =MAX(A7:A49)-MIN(A7:A49) Worked perfectly !!
    Thanks very much for your quick response.
    Your help is very appreciated.

    Mark

  19. I am working with dates before 1900. Using them in a calculation for example (each of these in a seperate cell) 1/7/1865 then 12/6/1865 wanting the difference. I can’t get excel 97 or 2000 to recognize these as dates.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Terms and Conditions of use

The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.

Visit Us On TwitterVisit Us On FacebookVisit Us On Google PlusVisit Us On Youtube