ExcelBanter - Convert dates

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Convert dates (https://www.excelbanter.com/excel-programming/443666-convert-dates.html)

How can I convert a date with custom number format mm/dd/yyyy
(example: 06/23/2000) to a serial number?

How can I convert a date with a general format (example: 10-01-2003)
to a serial number?

"gcotterl" wrote in message
...
How can I convert a date with custom number format mm/dd/yyyy
(example: 06/23/2000) to a serial number?

How can I convert a date with a general format (example: 10-01-2003)
to a serial number?

Let me direct you to a specific page in Chip Pearson's website
http://www.cpearson.com/Excel/datetime.htm#AddingDates

On Sep 27, 10:45*pm, gcotterl wrote:
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.

The cells in Col C contain:

06/28/1991
06/25/1991
05/13/1991
06/21/1991
06/30/1992
11/07/1990

In Format Cells:
"Category" = Custom
"Type" = mm/dd/yyyy

===================

The cells in Col G contain:

09-30-2003
10-20-2005
09-30-2003
09-30-2003
04-07-2000
06-06-2003

In Format Cells:
"Category" = General
(The first two digits is the Month; the middle two digits is the Day;
the last four digits is the Year)

===============

I want to determine if G1 is after (i.e., greater than) C1.

"gcotterl" wrote in message
...
The cells in Col C contain:

06/28/1991
06/25/1991
05/13/1991
06/21/1991
06/30/1992
11/07/1990

In Format Cells:
"Category" = Custom
"Type" = mm/dd/yyyy

===================

The cells in Col G contain:

09-30-2003
10-20-2005
09-30-2003
09-30-2003
04-07-2000
06-06-2003

In Format Cells:
"Category" = General
(The first two digits is the Month; the middle two digits is the Day;
the last four digits is the Year)

===============

I want to determine if G1 is after (i.e., greater than) C1.

=G1C1
will work if the cells contain dates. No further conversion needed.

HTH. Best wishes Harald

On Sep 28, 8:18*am, "Harald Staff" wrote:
"gcotterl" wrote in message

...

The cells in Col C contain:

06/28/1991
06/25/1991
05/13/1991
06/21/1991
06/30/1992
11/07/1990

In Format Cells:
"Category" = Custom
"Type" = mm/dd/yyyy

===================

The cells in Col G contain:

09-30-2003
10-20-2005
09-30-2003
09-30-2003
04-07-2000
06-06-2003

In Format Cells:
"Category" = General
(The first two digits is the Month; the middle two digits is the Day;
the last four digits is the Year)

===============

I want to determine if G1 is after (i.e., greater than) C1.

=G1C1
will work if the cells contain dates. No further conversion needed.

HTH. Best wishes Harald- Hide quoted text -

- Show quoted text -

Hi Harald,

The results of =G1C1 are not correct:

C G =G1C1

06/28/1991 09-30-2003 FALSE
06/25/1991 09-30-2003 FALSE
05/13/1991 09-30-2003 FALSE
06/21/1991 09-30-2003 TRUE
06/30/1992 09-30-2003 TRUE
11/07/1990 09-30-2003 TRUE
06/20/1993 09-30-2003 TRUE
03/26/1991 09-30-2003 TRUE

On Tue, 28 Sep 2010 06:14:28 -0700 (PDT), gcotterl
wrote:

The cells in Col C contain:

06/28/1991
06/25/1991
05/13/1991
06/21/1991
06/30/1992
11/07/1990

In Format Cells:
"Category" = Custom
"Type" = mm/dd/yyyy

===================

The cells in Col G contain:

09-30-2003
10-20-2005
09-30-2003
09-30-2003
04-07-2000
06-06-2003

In Format Cells:
"Category" = General
(The first two digits is the Month; the middle two digits is the Day;
the last four digits is the Year)

===============

I want to determine if G1 is after (i.e., greater than) C1.

And what is the result of the =TYPE(cell_ref) that joeu2004 asked you
to report?

On Sep 28, 10:55*am, Ron Rosenfeld wrote:
On Tue, 28 Sep 2010 06:14:28 -0700 (PDT), gcotterl

wrote:
The cells in Col C contain:

06/28/1991
06/25/1991
05/13/1991
06/21/1991
06/30/1992
11/07/1990

In Format Cells:
"Category" = Custom
"Type" = mm/dd/yyyy

===================

The cells in Col G contain:

09-30-2003
10-20-2005
09-30-2003
09-30-2003
04-07-2000
06-06-2003

In Format Cells:
"Category" = General
(The first two digits is the Month; the middle two digits is the Day;
the last four digits is the Year)

===============

I want to determine if G1 is after (i.e., greater than) C1.

And what is the result of the =TYPE(cell_ref) that joeu2004 asked you
to report?- Hide quoted text -

- Show quoted text -

==================================================

C =TYPE(cell_ref) result

06/28/1991 =TYPE(C1) 01/02/1900
06/25/1991 =TYPE(C2) 01/01/1900
05/13/1991 =TYPE(C3) 01/01/1900
06/21/1991 =TYPE(C4) 01/01/1900
06/30/1992 =TYPE(C5) 01/01/1900
11/07/1990 =TYPE(C6) 01/01/1900

G =TYPE(cell_ref) result
09-30-2003 =TYPE(G1) 2
09-30-2003 =TYPE(G2) 1
09-30-2003 =TYPE(G3) 1
09-30-2003 =TYPE(G4) 2
09-30-2003 =TYPE(G5) 2
09-30-2003 =TYPE(G6) 2

On Tue, 28 Sep 2010 11:45:59 -0700 (PDT), gcotterl
wrote:

On Sep 28, 10:55*am, Ron Rosenfeld wrote:
On Tue, 28 Sep 2010 06:14:28 -0700 (PDT), gcotterl

And what is the result of the =TYPE(cell_ref) that joeu2004 asked you
to report?- Hide quoted text -

- Show quoted text -

================================================= =

C =TYPE(cell_ref) result

06/28/1991 =TYPE(C1) 01/02/1900
06/25/1991 =TYPE(C2) 01/01/1900
05/13/1991 =TYPE(C3) 01/01/1900
06/21/1991 =TYPE(C4) 01/01/1900
06/30/1992 =TYPE(C5) 01/01/1900
11/07/1990 =TYPE(C6) 01/01/1900

G =TYPE(cell_ref) result
09-30-2003 =TYPE(G1) 2
09-30-2003 =TYPE(G2) 1
09-30-2003 =TYPE(G3) 1
09-30-2003 =TYPE(G4) 2
09-30-2003 =TYPE(G5) 2
09-30-2003 =TYPE(G6) 2

OK, this clarifies things.

The problem is that your data is really screwed up.

Excel stores dates as serial numbers where 1 = 1 Jan 1900. Some of
your dates are proper dates (those where the TYPE function returns a
1, or returns 01/01/1900 which represents the same value); and other
of your dates have been stored as TEXT representations of a date.

Excel cannot usually perform proper mathematical or comparison
operations on dates that are stored as TEXT.

For this workbook, the first thing you should do is to be certain that
all of the values that appear as dates, are "real" Excel dates (and by
real I mean they are stored as a number as I described above).

I think the simplest way of doing this will be to select the range of
dates in Column C.

Then from the main menu (or Ribbon), select
Data/Text to Columns
<Next
<Next
Now at Step 3:
Column data Format: Date: MDY
<Finish

Repeat this after selecting the dates in Column G.

The above should convert all of your dates to "real" Excel dates, and
allow you to do comparisons or various mathematical operations.

If you cannot clean up your data before getting it into Excel, you
will need to do this whenever you bring dates into your worksheet.