Getting date value from custom cell format

[davegugg]

Hi

I have a column of cells that are formatted as custom: mm/dd/yyy.
I want to use a worksheet function to turn them into a date value.
DATEVALUE() does not work. Is there any other way to do this?

Thanks

Dave

[NBVC]

Can you give an example of what you mean by converting to a datevalue?

[davegugg]

Hmm, that is tough to do.
I'm working with a separate program that runs through excel. It is giving me an output with a column of dates, but they are custom formatted. When I try to set up a filter, it doesn't work because it sees them as text, but I need them numbers, so I can only include records >= a certain date.

[NBVC]

Maybe you can try this.

Select the column and go to data|text to columns.. skip to 3rd window and select Date from the column data format area, then choose MYD from adjacent drop down... Click Finish.. does it fix?

[davegugg]

That does change them, but I need to use a worksheet function because I need the program to do calculations on them before it creates the output.

I've posted an example that has my custom format and the example of the datevalue() result. Same thing happens for Value().

[NBVC]

I am still not grasping what you want to achieve.. if you simply enter =A1 you get the same value.. if you format it as General you will get the serial number representation of the date...

Maybe you can tell us what you ultimately need to do.. ie describe what your final formula needs to be doing...

[davegugg]

Maybe there is a different issue at fault, let me play around some more with it and if I need more help I'll ask.

Thank you very much for your time and effort!
God Bless