Excel formula help please

Vitor

DATEDIF is available from Excel 2000

Gers

Heedtheadvice said:

Having a #Name error, I wonder if you do not have that in your formula list ( click the = sign to find out ) or perhaps, did you use the double quote symbol or two single quotes?

Yes, used double quotation marks rather than single ones.

double_dutchy said:

As others have said, DATEDIF probably isn't the best function to use here in any case.....but if you get #NAME? error as shown then that might mean there's something wrong with the quote marks in the "d" part (they look a little odd on your screenshot).

Did you say you copied the formula? What happens if you just type it in afresh, does it work then?

I didn't copy and paste, typed it fresh using the double quotes.

Even though it's sorted now I will try it again with the single quotes.  I appreciate the advice.

Gers

No, using two single quote marks made no difference.

Earlier I used the same DATEDIF with a "Y" to calculate ages from birthdate to date which worked perfectly.

Once again, thanks for all the help and advice, especially with the easy / correct way to do it.

double_dutchy

Seems odd that DATEDIF would work with "y" but not "d". I don't know if it might be a language issue - do you have windows and/or excel set to use English?

Heedtheadvice

Vitor said:

DATEDIF is available from Excel 2000

Sorry that is just wrong. I was using it before year 2004 and it was to be comparable to Lotus 123 in early excel programs ( but I cannot remember from when prior to 2004)

A simple online search shows it was in place well before Excel 2000 such as in another forum' thread by a user

"

davesexcel 01:20 AM 10-15-2006

Here is a write up on excel 2003 formulas, Datedif is included

38. Quickly determine someone's age. Using the DATEDIF() function, you can plug in a person's birthday and instantly see that person's age. Open a blank worksheet and type the person's birth date in cell A1, using slashes to separate the day, month, and year. Select cell A2, type =DATEDIF(A1,TODAY(),"y"), and press ENTER to see the person's current age in cell A2."

Heedtheadvice

Gers said:

No, using two single quote marks made no difference.

Earlier I used the same DATEDIF with a "Y" to calculate ages from birthdate to date which worked perfectly.

Once again, thanks for all the help and advice, especially with the easy / correct way to do it.

Hmm  in what you show there the difference in quote narks still exists.

I thought dutchy was on the right track with the 'copied' question!

It does seem odd that you works but d does not. I still feel it could be the marks difference. Can you try to replace 'y' with 'd' in the formula that works  and visa versa. That will prove or disprove if it is the quotation mark character that is the cause.

fifeken

Agree on the quote marks comment.  The ones in the OP and the "working" version with a "Y" are different,

Gers

fifeken said:

Agree on the quote marks comment.  The ones in the OP and the "working" version with a "Y" are different,

Interesting and odd! Looks like Word and Excel have different formats for the quotation marks. 

 

And a simple copy, paste and replace Y with D doesn't work either.

double_dutchy

In that last example #NUM! is the expected answer because A1 is blank, what do you get using this version?

=DATEDIF(A2,B2,"d")

Heedtheadvice

Yes that formula is in error.

You did not do a side replacement so really nothing there has been has been gained

Maybe correct the formula?

"Interesting and odd! Looks like Word and Excel have different formats for the quotation marks.  "

yes interesting but not particularly relevant as you did not copy from Word into Excel but merely typed into Excel did you not?