Datevalue(cell ref)=#VALUE!

[telephoto]

This is silly.

Col A is formated as Date under custom formats (in the UK dates are) dd/mm/yyyy

I want to count the difference in days between cells, so while setting it up B1 = Datevalue(A1)
I was expecting the Excel date expressed as a number, what I got was #VALUE!

While setting the formula using fx it shows the result on the right - and that's a number, just what I want.

Can someone please tell me where either I or the program is going wrong?

Telephoto

(Duane - congrats on tipmaster!)

 

[SkipVought]

hi,

The FORMAT is totally irrelevant. REAL DATES are numbers and all a format does is DISPLAY another value, and nothing in the underlying values are ever changed!

What happens if your change the format in column A to GENERAL? If you have REAL DATES in column A, then ALL the display values will be NUMBERS. If you have any remaining cells containing TEXT, then they are not real dates.

DateValue is used to CONVERT from STRING to NUMERIC, if the STRING represents a date AND the structure is yyyy/mm/dd or mm/dd/yyyy. It does NOT PROPERLY convert strings representing dates structured dd/mm/yyyy.

If ALL your values in column A are REAL DATES, then there is no need to use the DateValue function.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

And try this test, also. I 'm curious of the result.

In a cell formatted yyyy/mm/dd, enter
[tt]
1/3
[/tt]
and observe what is displayed.

I believe that you will see a different result than you expect, but I could be wrong.

faq68-5827

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[telephoto]

Hi Skip

Thank you for responding. Your pointer to the link, and its pointer to microsoft eventually made me twig my error.

Formatting is crucial.

I wanted to count days between two entries, DATEVALUE giving the Excel date as a number. This works (and for UK date strings as well) ONLY if the cell containing the date is specifically formatted as Text.



T

 

[Gavona]

Hi Telephoto,
You don't appear to have had many helpful responses judging by the number of stars you have awarded.
[purple]*[/purple]Thank SkipVought
for this valuable post!

Gavin