How can I convert a date with custom number format mm/dd/yyyy
(example: 06/23/2000) to a serial number?
Format as General or Number.
But why would you do that?
I ask only because I wonder if you have some misunderstanding. How a
date value appears usually does not affect how you can use it.
How can I convert a date with a general format (example: 10-01-2003)
to a serial number?
First, you need to explain whether 10 is the month or day. Since you
mentioned mm/dd/yyyy above, I will assume it is the day of the month.
Second, you need to describe what type the data __value__ is
initially. What is the result of TYPE(A1): 1 (numeric) or 2 (text).
"General format" is a numeric format. But I suspect you mean that
10-01-2003 is text.
Finally, you need to describe __all_possible__ forms the data can
take. If the day is less than 10, would it appear as 5-01-2003 or
05-01-2003?
The answer to the latter determines just how easy or hard the
conversion can be.
Assuming you have text in the form of dd-mm-yyyy -- that is, the day
and month are always 2 digits -- the "easiest" way to convert it
without having to take Regional and Language control panel settings
into account is:
=date(right(A1,4),mid(A1,4,2),left(A1,2))
If you want to replace the original text with the converted serial
number, copy the result above, then use paste-special value-and-number-
formats to replace A1.