Datedif returns Name error

Topic

New Reply

I’m trying to use datedif to calculate the difference between two dates. It seems to be missing from my function list. I have the analysis toolpak installed. hmmm any ideas?

What I want to do is calc the diff between two dates, and if the dates are the same date, return a 1 instead of a zero. Am I correct in thinking datedif will give me this?

Reply | Quote

Replies

DATEDIF is built into Excel but it is not documented officially (a very strange omission by Microsoft).

See DATEDIF Worksheet Function for the syntax.

When the two dates are the same, DATEDIF will return 0, so you’d have to add 1 if you want the result to be 1.

Note: if you want to calculate the number of days between two dates, you don’t need DATEDIF. You can simply subtract the dates, e.g.

=B1-A1

Excel tends to format the result as a date; to see the number of days set the number format of the cell with the formula to General.

Reply | Quote

thanks!

Reply | Quote

What I want to do is calc the diff between two dates, and if the dates are the same date, return a 1 instead of a zero. Am I correct in thinking datedif will give me this?

if you are not interested in what the actual difference is, and just want a 0/1 returned, you could try this – assuming the dates are in A1 & B1
=1*(A1=B1) which gives 1 if the dates are the same else giving 0

If you do want to know the actual difference
=if(A1=B1,1,A1-B1) gives 1 if the dates are the same, and the difference if they are not. If using this method the next question would be how to tell whether a result of 1 represents the same date in both fields or successive dates – conditional formatting anyone?

Reply | Quote

Sometimes people want to count the number of days in the range Date1 … Date2.
If Date1 = Date2, there is 1 day.
If Date1 and Date2 are consecutive days, there are 2 days, etc.

This can be calculated as =Date2-Date1+1

Reply | Quote