• Hi,

    I have a worksheet with Date month and year in column A and i want in empty column B the interval in days .(ie the difference in Days). Thanks for help.


  • Hi Manzoor

    Enter =TODAY() into any cell, name it "Today" then use:


    Format cell as General



    Format cell as General

    See this link: http://www.ozgrid.com/Excel/ExcelDateandTimes.htm for valid Excel dates and times explained

  • Hi,

    I have tried this formula but i don't why it is not working. I am attaching a sample file. There is Date column in "D". Appreciate your help. Thanks


  • The problem is that you do not have an Excel recognizable date entered into column D. The entries in column D have a ' before them, which makes them text. To use the date functions Excel needs actual dates, and to get a difference in days, it needs the reference date to include the day. (You can set the cell format to display only the month and year, but the actual date stored in the cell must be a full day-month-year entry to let the DATEDIF ot the TODAY()-cell formulas to work.)

    Incidently, I think the Analysis Add-In must be active for the DATEDIF function to work (it comes with it, I think).

    Hope this helps.

  • "The problem is that you do not have an Excel recognizable date entered into column D. "

    2 solutions:

    1) use a formula that coerces the text formatted date into a recognised data value, then embed this in the calculation. Given the posted data:


    ...which assumes that data like Sep-04 can be interpreted as 01/09/04. also note that given dates both before & after today, datedif is not appropriate because of it's sytntax of datedif(earlydate, latedate,period)

    2) coerce the text formatted date values to proper dates using text to columns -
    highlight column | go to data | text to columns | hit next twice | select DMY from the date format options| hit finish.

  • Hi what if I do not want to take into account the number of days but only the number of months, becaus I found out that from

    It is only considered as 1 mth in

    Is there anyway I can leave out the DATE part and enter only the month and the yr? Thank you.

  • That precise question cameup on another thread today. Here is the link. I think the sample I posted there will answer your question.


    If you need to "ROUND" to the nearest whole month, consider using the day function on each date and rounding up or down depending on whetherthe difference is greater than or less than say 15 days.

    EDIT: Just noticed that the thread I referrenced was yours. Please do not post the same question in two places (start multiple duplicate threads) as this gets folks confused and scatters (rather than consolidates) information on common topics.

  • Thank you and sorry for the repost. Because I was looking thru the threads and saw the thing I needed so I thought I should post here to ask :D sorry about that.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!