why does the dateif function result in an error in Excel 2003?

  • Thread starter Thread starter Cheryl
  • Start date Start date
Perhaps you should show us the formula used and the error message?
The second date should be later than the first
best wishes
 
Firstly, please ask your question in the body of the message, not in the
text line.

Secondly, the reason for the error is that Excel does not have a DATEIF
function. Did you mean DATEDIF?
 
Hi,

Please enter a quesion in the body of your emai; if you don't the web won't
let us reply, instead it displays the following error message.

"An error occurred while sending your post

We're sorry, but there was a problem with the system and your post was not
received. The error has been reported to Operations and will be investigated
as soon as possible. Please try again later."

This means, that to respond we need to start a separate thread - which is a
bad practice and wastes time.

Access has a DateDiff function and Excel has a DateDif funtion althought
they do about the same thing neither has a dateif function although you can
make a Date(IF( calculation.

If you don't put the arguments in the correct order you will get an error,
if you don't put quotes around the text entered constants you will get an
error, if you misspell the function name you will get an error, if you use
non-dates in the date arguments you will get an error.


When you post questions to the newsgroups it generally a good idea to
include at least:

1. Your version of Excel
2. Any formulas you are having problems with
3. A complete description of any error messages you are getting
4. Samples of the data layout; and based on that sample what you want to
return


The undocumented DATEDIF function has the following form:

DATEDIF(StartDate,EndDate,Unit)

Where Units are on the left in the table below and their results on the
right. Note that the Unit must be quoted - "y" for example.

y Whole years between two dates
m Whole months between two dates
d Whole days between two dates
md Number of days between two dates ignoring months
ym Number of months between two dates ignoring years
yd Number of days between two dates ignoring years

=DATEDIF(A1,A2,"Y") returns the number of whole years between the dates in
cell A1 and A2

For reasons unknown Microsoft only choose to document this function in Excel
version 2000.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire


David Biddulph said:
Firstly, please ask your question in the body of the message, not in the
text line.

Secondly, the reason for the error is that Excel does not have a DATEIF
function. Did you mean DATEDIF?
Click to expand...
[/QUOTE]
 
Shane,
md Number of days between two dates ignoring months (sometimes)
Click to expand...

Try these 2 dates with this formula

31/01/2009 01/03/2009

=DATEDIF(D5,E5,"md")

And that's why I think it isn't documented.

Mike

Shane Devenshire said:
Hi,

Please enter a quesion in the body of your emai; if you don't the web won't
let us reply, instead it displays the following error message.

"An error occurred while sending your post

We're sorry, but there was a problem with the system and your post was not
received. The error has been reported to Operations and will be investigated
as soon as possible. Please try again later."

This means, that to respond we need to start a separate thread - which is a
bad practice and wastes time.

Access has a DateDiff function and Excel has a DateDif funtion althought
they do about the same thing neither has a dateif function although you can
make a Date(IF( calculation.

If you don't put the arguments in the correct order you will get an error,
if you don't put quotes around the text entered constants you will get an
error, if you misspell the function name you will get an error, if you use
non-dates in the date arguments you will get an error.


When you post questions to the newsgroups it generally a good idea to
include at least:

1. Your version of Excel
2. Any formulas you are having problems with
3. A complete description of any error messages you are getting
4. Samples of the data layout; and based on that sample what you want to
return


The undocumented DATEDIF function has the following form:

DATEDIF(StartDate,EndDate,Unit)

Where Units are on the left in the table below and their results on the
right. Note that the Unit must be quoted - "y" for example.

y Whole years between two dates
m Whole months between two dates
d Whole days between two dates
md Number of days between two dates ignoring months
ym Number of months between two dates ignoring years
yd Number of days between two dates ignoring years

=DATEDIF(A1,A2,"Y") returns the number of whole years between the dates in
cell A1 and A2

For reasons unknown Microsoft only choose to document this function in Excel
version 2000.
--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire
Click to expand...
[/QUOTE]
 
Back
Top