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

Hi Manzoor,

Try something like this:

=DATEDIF(A1,A2,"d")

HTH

Hi Manzoor

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

=Today-A1

Format cell as General

OR

=DATEDIF(A1,Today,"d")

Format cell as General

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

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.

http://www.ozgrid.com/forum/viewthread.php?tid=4819

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.

Quote

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

http://www.ozgrid.com/forum/viewthread.php?tid=4819

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.

Display More

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 sorry about that.