# [Solved] Formulas: DIFFERENCE BETWEEN A DATE & TODAY DAT

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

Manzoor

• 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

Manzoor

• Sorry i forget to attach my file.

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

=(TEXT(D2,"DDMMMYYYY")+0)-TODAY()

...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
20/05/03
to
13/07/03

It is only considered as 1 mth in
DATEDIF(A1,B1,"m")

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.