If statement

Does anyone have a solution?

Subject: If statement
From: "Doug"
Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the value is
Text and can't
be coerced into a date/time.

What is the exact format of the date/time?


Thanks for your help JE,
When I use INT I get a #VALUE response whether or not the
date is current. This is what I used: =IF(INT(M35)=TODAY
(),0,5). M35 is the cell that contains both the date and
time in the following format: 6/24/04 17:00.

What can I do to get a response of 0 when the date is
current and 5 when it is not.
Doug



Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base date, and
times as
fractional days (so 3:00 = 0.125). So to ignore times, use
INT():

=IF(INT(A1)=TODAY(),"It's Today!", "It's not Today.")

In article ,
"Doug" wrote:



Subject: If statement
From: "Doug"
Sent: 6/24/2004
9:10:43 PM


A document generated at work has a column which contains
both a date and time in each cell. I am trying to build an
IF statement that will look at the date only and ignore
the time, then provided a specific value if it is the
current date and something else if it is not. When I
delete the time from the cell and just leave the date, the
IF statement works perfectly. When I leave the time there
the IF statement sees it as a different value and does not
work right. Since I am not the author of this report, I
can not change it to place the time in a different cell
from the date. Is there any way I can have the IF
statement ignore the time so that it just uses the date?
Doug
..

The format category is general.

If you have 6/24/04 17:00 entered in a cell and formatted
as general then it is not being recognized as a date and
is therefor text. If you enter a recognized date string
such as 6/24/04 17:00, Excel will automatically format it
as a date/time. If you then change that format to a
general format, the new displayed value in that cell will
be 38162.70833.

38162 is the numeric value of the date. It's the 38162 nd
day since 1/1/1900.

..70833 is the numeric value of the time. It's the
fractional part of a day (24 hr).

Is this "date string" manually entered? Is it copy/pasted
from a different source? Copy/pasting is notorious for
including unseen characters which can cause just the
problem your describing.

Biff

-----Original Message-----
Does anyone have a solution?

Subject: If statement
From: "Doug"
Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the value is
Text and can't
be coerced into a date/time.

What is the exact format of the date/time?


Thanks for your help JE,
When I use INT I get a #VALUE response whether or not the
date is current. This is what I used: =IF(INT(M35)=TODAY
(),0,5). M35 is the cell that contains both the date and
time in the following format: 6/24/04 17:00.

What can I do to get a response of 0 when the date is
current and 5 when it is not.
Doug



Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base date, and
times as
fractional days (so 3:00 = 0.125). So to ignore times,
use
INT():

=IF(INT(A1)=TODAY(),"It's Today!", "It's not Today.")

In article ,
"Doug" wrote:



Subject: If statement
From: "Doug"
Sent: 6/24/2004
9:10:43 PM


A document generated at work has a column which contains
both a date and time in each cell. I am trying to build
an
IF statement that will look at the date only and ignore
the time, then provided a specific value if it is the
current date and something else if it is not. When I
delete the time from the cell and just leave the date,
the
IF statement works perfectly. When I leave the time there
the IF statement sees it as a different value and does
not
work right. Since I am not the author of this report, I
can not change it to place the time in a different cell
from the date. Is there any way I can have the IF
statement ignore the time so that it just uses the date?
Doug
..


.

Biff,
The data is copied from a report and then pasted into
excel. I don't have control over how the report is
formated, but I do have control of what I do with it once
it is pasted into excel.

What can I do so that it will recognize the date and
ignore the time, without actually deleting the time?
Thanks,
Doug

-----Original Message-----
The format category is general.

If you have 6/24/04 17:00 entered in a cell and formatted
as general then it is not being recognized as a date and
is therefor text. If you enter a recognized date string
such as 6/24/04 17:00, Excel will automatically format it
as a date/time. If you then change that format to a
general format, the new displayed value in that cell will
be 38162.70833.

38162 is the numeric value of the date. It's the 38162 nd
day since 1/1/1900.

..70833 is the numeric value of the time. It's the
fractional part of a day (24 hr).

Is this "date string" manually entered? Is it copy/pasted
from a different source? Copy/pasting is notorious for
including unseen characters which can cause just the
problem your describing.

Biff

-----Original Message-----
Does anyone have a solution?

Subject: If statement
From: "Doug"
Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the value is
Text and can't
be coerced into a date/time.

What is the exact format of the date/time?


Thanks for your help JE,
When I use INT I get a #VALUE response whether or not
the
date is current. This is what I used: =IF(INT(M35)=TODAY
(),0,5). M35 is the cell that contains both the date and
time in the following format: 6/24/04 17:00.

What can I do to get a response of 0 when the date is
current and 5 when it is not.
Doug



Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base date, and
times as
fractional days (so 3:00 = 0.125). So to ignore times,
use
INT():

=IF(INT(A1)=TODAY(),"It's Today!", "It's not Today.")

In article ,
"Doug" wrote:



Subject: If statement
From: "Doug"
Sent: 6/24/2004
9:10:43 PM


A document generated at work has a column which contains
both a date and time in each cell. I am trying to build
an
IF statement that will look at the date only and ignore
the time, then provided a specific value if it is the
current date and something else if it is not. When I
delete the time from the cell and just leave the date,
the
IF statement works perfectly. When I leave the time
there
the IF statement sees it as a different value and does
not
work right. Since I am not the author of this report, I
can not change it to place the time in a different cell
from the date. Is there any way I can have the IF
statement ignore the time so that it just uses the date?
Doug
..


.

.

You stated that:
"but I do have control of what I do with it once it is pasted into
excel."

So, do a "TextToColumns", convert the imported TEXT data to a true date and
time, placing the "converted data" in an out of the way portion of the
sheet, and reference your formulas to this converted (revised) (hidden) data
column.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
wrote in message
...
Biff,
The data is copied from a report and then pasted into
excel. I don't have control over how the report is
formated, but I do have control of what I do with it once
it is pasted into excel.

What can I do so that it will recognize the date and
ignore the time, without actually deleting the time?
Thanks,
Doug

-----Original Message-----
The format category is general.

If you have 6/24/04 17:00 entered in a cell and formatted
as general then it is not being recognized as a date and
is therefor text. If you enter a recognized date string
such as 6/24/04 17:00, Excel will automatically format it
as a date/time. If you then change that format to a
general format, the new displayed value in that cell will
be 38162.70833.

38162 is the numeric value of the date. It's the 38162 nd
day since 1/1/1900.

..70833 is the numeric value of the time. It's the
fractional part of a day (24 hr).

Is this "date string" manually entered? Is it copy/pasted
from a different source? Copy/pasting is notorious for
including unseen characters which can cause just the
problem your describing.

Biff

-----Original Message-----
Does anyone have a solution?

Subject: If statement
From: "Doug"
Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the value is
Text and can't
be coerced into a date/time.

What is the exact format of the date/time?


Thanks for your help JE,
When I use INT I get a #VALUE response whether or not
the
date is current. This is what I used: =IF(INT(M35)=TODAY
(),0,5). M35 is the cell that contains both the date and
time in the following format: 6/24/04 17:00.

What can I do to get a response of 0 when the date is
current and 5 when it is not.
Doug



Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base date, and
times as
fractional days (so 3:00 = 0.125). So to ignore times,
use
INT():

=IF(INT(A1)=TODAY(),"It's Today!", "It's not Today.")

In article ,
"Doug" wrote:



Subject: If statement
From: "Doug"
Sent: 6/24/2004
9:10:43 PM


A document generated at work has a column which contains
both a date and time in each cell. I am trying to build
an
IF statement that will look at the date only and ignore
the time, then provided a specific value if it is the
current date and something else if it is not. When I
delete the time from the cell and just leave the date,
the
IF statement works perfectly. When I leave the time
there
the IF statement sees it as a different value and does
not
work right. Since I am not the author of this report, I
can not change it to place the time in a different cell
from the date. Is there any way I can have the IF
statement ignore the time so that it just uses the date?
Doug
..


.

.

OR -

=INT(VALUE(A1)) and format as date

Copy an MT cell, select the text date string, Paste
SpecialAddOK

These examples will convert *text numbers* into *numeric
numbers*.

Biff

-----Original Message-----

You stated that:
"but I do have control of what I do with it once it is
pasted into
excel."

So, do a "TextToColumns", convert the imported TEXT data
to a true date and
time, placing the "converted data" in an out of the way
portion of the
sheet, and reference your formulas to this converted
(revised) (hidden) data
column.
--
HTH,

RD

----------------------------------------------------------
-----------------
Please keep all correspondence within the NewsGroup, so
all may benefit !
----------------------------------------------------------
-----------------
wrote in message
...
Biff,
The data is copied from a report and then pasted into
excel. I don't have control over how the report is
formated, but I do have control of what I do with it
once
it is pasted into excel.

What can I do so that it will recognize the date and
ignore the time, without actually deleting the time?
Thanks,
Doug

-----Original Message-----
The format category is general.

If you have 6/24/04 17:00 entered in a cell and
formatted
as general then it is not being recognized as a date
and
is therefor text. If you enter a recognized date string
such as 6/24/04 17:00, Excel will automatically format
it
as a date/time. If you then change that format to a
general format, the new displayed value in that cell
will
be 38162.70833.

38162 is the numeric value of the date. It's the 38162
nd
day since 1/1/1900.

..70833 is the numeric value of the time. It's the
fractional part of a day (24 hr).

Is this "date string" manually entered? Is it
copy/pasted
from a different source? Copy/pasting is notorious for
including unseen characters which can cause just the
problem your describing.

Biff

-----Original Message-----
Does anyone have a solution?

Subject: If statement
From: "Doug"
Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the value
is
Text and can't
be coerced into a date/time.

What is the exact format of the date/time?


Thanks for your help JE,
When I use INT I get a #VALUE response whether or not
the
date is current. This is what I used: =IF(INT(M35)
=TODAY
(),0,5). M35 is the cell that contains both the date
and
time in the following format: 6/24/04 17:00.

What can I do to get a response of 0 when the date is
current and 5 when it is not.
Doug



Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base date,
and
times as
fractional days (so 3:00 = 0.125). So to ignore times,
use
INT():

=IF(INT(A1)=TODAY(),"It's Today!", "It's not
Today.")

In article ,
"Doug" wrote:



Subject: If statement
From: "Doug"
Sent: 6/24/2004
9:10:43 PM


A document generated at work has a column which
contains
both a date and time in each cell. I am trying to
build
an
IF statement that will look at the date only and
ignore
the time, then provided a specific value if it is the
current date and something else if it is not. When I
delete the time from the cell and just leave the date,
the
IF statement works perfectly. When I leave the time
there
the IF statement sees it as a different value and does
not
work right. Since I am not the author of this report,
I
can not change it to place the time in a different
cell
from the date. Is there any way I can have the IF
statement ignore the time so that it just uses the
date?
Doug
..


.

.


.

One of the advantages of TTC, is no formulas to create and copy, then erase
or convert, since the TTC result (return) is instant, usable data for the
entire selected range.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Biff" wrote in message
...
OR -

=INT(VALUE(A1)) and format as date

Copy an MT cell, select the text date string, Paste
SpecialAddOK

These examples will convert *text numbers* into *numeric
numbers*.

Biff

-----Original Message-----

You stated that:
"but I do have control of what I do with it once it is
pasted into
excel."

So, do a "TextToColumns", convert the imported TEXT data
to a true date and
time, placing the "converted data" in an out of the way
portion of the
sheet, and reference your formulas to this converted
(revised) (hidden) data
column.
--
HTH,

RD

----------------------------------------------------------
-----------------
Please keep all correspondence within the NewsGroup, so
all may benefit !
----------------------------------------------------------
-----------------
wrote in message
...
Biff,
The data is copied from a report and then pasted into
excel. I don't have control over how the report is
formated, but I do have control of what I do with it
once
it is pasted into excel.

What can I do so that it will recognize the date and
ignore the time, without actually deleting the time?
Thanks,
Doug

-----Original Message-----
The format category is general.

If you have 6/24/04 17:00 entered in a cell and
formatted
as general then it is not being recognized as a date
and
is therefor text. If you enter a recognized date string
such as 6/24/04 17:00, Excel will automatically format
it
as a date/time. If you then change that format to a
general format, the new displayed value in that cell
will
be 38162.70833.

38162 is the numeric value of the date. It's the 38162
nd
day since 1/1/1900.

..70833 is the numeric value of the time. It's the
fractional part of a day (24 hr).

Is this "date string" manually entered? Is it
copy/pasted
from a different source? Copy/pasting is notorious for
including unseen characters which can cause just the
problem your describing.

Biff

-----Original Message-----
Does anyone have a solution?

Subject: If statement
From: "Doug"
Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the value
is
Text and can't
be coerced into a date/time.

What is the exact format of the date/time?


Thanks for your help JE,
When I use INT I get a #VALUE response whether or not
the
date is current. This is what I used: =IF(INT(M35)
=TODAY
(),0,5). M35 is the cell that contains both the date
and
time in the following format: 6/24/04 17:00.

What can I do to get a response of 0 when the date is
current and 5 when it is not.
Doug



Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base date,
and
times as
fractional days (so 3:00 = 0.125). So to ignore times,
use
INT():

=IF(INT(A1)=TODAY(),"It's Today!", "It's not
Today.")

In article ,
"Doug" wrote:



Subject: If statement
From: "Doug"
Sent: 6/24/2004
9:10:43 PM


A document generated at work has a column which
contains
both a date and time in each cell. I am trying to
build
an
IF statement that will look at the date only and
ignore
the time, then provided a specific value if it is the
current date and something else if it is not. When I
delete the time from the cell and just leave the date,
the
IF statement works perfectly. When I leave the time
there
the IF statement sees it as a different value and does
not
work right. Since I am not the author of this report,
I
can not change it to place the time in a different
cell
from the date. Is there any way I can have the IF
statement ignore the time so that it just uses the
date?
Doug
..


.

.


.

I tried doing a text to column convert, but the date and
time didn't separate into two columns.

I moved the data from one of the cells off to the side. I
selected the new cell with the data in it and clicked on
data and then text to columns. I selected delimited and
next and choose space as the delimiter, since there is a
space between the date and time. I formatted with date and
when I clicked on finish I didn't notice any changes, both
date and time remained in one cell.

Why didn't it separate into two columns and what can I do
to get it to work right?

Thanks for your help,
Doug

-----Original Message-----

You stated that:
"but I do have control of what I do with it once it is
pasted into
excel."

So, do a "TextToColumns", convert the imported TEXT data
to a true date and
time, placing the "converted data" in an out of the way
portion of the
sheet, and reference your formulas to this converted
(revised) (hidden) data
column.
--
HTH,

RD

----------------------------------------------------------
-----------------
Please keep all correspondence within the NewsGroup, so
all may benefit !
----------------------------------------------------------
-----------------
wrote in message
...
Biff,
The data is copied from a report and then pasted into
excel. I don't have control over how the report is
formated, but I do have control of what I do with it
once
it is pasted into excel.

What can I do so that it will recognize the date and
ignore the time, without actually deleting the time?
Thanks,
Doug

-----Original Message-----
The format category is general.

If you have 6/24/04 17:00 entered in a cell and
formatted
as general then it is not being recognized as a date
and
is therefor text. If you enter a recognized date string
such as 6/24/04 17:00, Excel will automatically format
it
as a date/time. If you then change that format to a
general format, the new displayed value in that cell
will
be 38162.70833.

38162 is the numeric value of the date. It's the 38162
nd
day since 1/1/1900.

..70833 is the numeric value of the time. It's the
fractional part of a day (24 hr).

Is this "date string" manually entered? Is it
copy/pasted
from a different source? Copy/pasting is notorious for
including unseen characters which can cause just the
problem your describing.

Biff

-----Original Message-----
Does anyone have a solution?

Subject: If statement
From: "Doug"
Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the value
is
Text and can't
be coerced into a date/time.

What is the exact format of the date/time?


Thanks for your help JE,
When I use INT I get a #VALUE response whether or not
the
date is current. This is what I used: =IF(INT(M35)
=TODAY
(),0,5). M35 is the cell that contains both the date
and
time in the following format: 6/24/04 17:00.

What can I do to get a response of 0 when the date is
current and 5 when it is not.
Doug



Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base date,
and
times as
fractional days (so 3:00 = 0.125). So to ignore times,
use
INT():

=IF(INT(A1)=TODAY(),"It's Today!", "It's not
Today.")

In article ,
"Doug" wrote:



Subject: If statement
From: "Doug"
Sent: 6/24/2004
9:10:43 PM


A document generated at work has a column which
contains
both a date and time in each cell. I am trying to
build
an
IF statement that will look at the date only and
ignore
the time, then provided a specific value if it is the
current date and something else if it is not. When I
delete the time from the cell and just leave the date,
the
IF statement works perfectly. When I leave the time
there
the IF statement sees it as a different value and does
not
work right. Since I am not the author of this report,
I
can not change it to place the time in a different
cell
from the date. Is there any way I can have the IF
statement ignore the time so that it just uses the
date?
Doug
..


.

.


.

When I try this, pointing to a cell that contains both the
date and time, I get #VALUE. If it is pointing to a blank
cell I get 1/0/00.

Why am I having this problem? How do I fix it?

Thanks for your help,
Doug


-----Original Message-----
OR -

=INT(VALUE(A1)) and format as date

Copy an MT cell, select the text date string, Paste
SpecialAddOK

These examples will convert *text numbers* into *numeric
numbers*.

Biff

-----Original Message-----

You stated that:
"but I do have control of what I do with it once it is
pasted into
excel."

So, do a "TextToColumns", convert the imported TEXT data
to a true date and
time, placing the "converted data" in an out of the way
portion of the
sheet, and reference your formulas to this converted
(revised) (hidden) data
column.
--
HTH,

RD

---------------------------------------------------------
-
-----------------
Please keep all correspondence within the NewsGroup, so
all may benefit !
---------------------------------------------------------
-
-----------------
wrote in message
.. .
Biff,
The data is copied from a report and then pasted into
excel. I don't have control over how the report is
formated, but I do have control of what I do with it
once
it is pasted into excel.

What can I do so that it will recognize the date and
ignore the time, without actually deleting the time?
Thanks,
Doug

-----Original Message-----
The format category is general.

If you have 6/24/04 17:00 entered in a cell and
formatted
as general then it is not being recognized as a date
and
is therefor text. If you enter a recognized date
string
such as 6/24/04 17:00, Excel will automatically
format
it
as a date/time. If you then change that format to a
general format, the new displayed value in that cell
will
be 38162.70833.

38162 is the numeric value of the date. It's the
38162
nd
day since 1/1/1900.

..70833 is the numeric value of the time. It's the
fractional part of a day (24 hr).

Is this "date string" manually entered? Is it
copy/pasted
from a different source? Copy/pasting is notorious for
including unseen characters which can cause just the
problem your describing.

Biff

-----Original Message-----
Does anyone have a solution?

Subject: If statement
From: "Doug"
Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the
value
is
Text and can't
be coerced into a date/time.

What is the exact format of the date/time?


Thanks for your help JE,
When I use INT I get a #VALUE response whether or not
the
date is current. This is what I used: =IF(INT(M35)
=TODAY
(),0,5). M35 is the cell that contains both the date
and
time in the following format: 6/24/04 17:00.

What can I do to get a response of 0 when the date is
current and 5 when it is not.
Doug



Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base date,
and
times as
fractional days (so 3:00 = 0.125). So to ignore
times,
use
INT():

=IF(INT(A1)=TODAY(),"It's Today!", "It's not
Today.")

In article ,
"Doug" wrote:



Subject: If statement
From: "Doug"
Sent: 6/24/2004
9:10:43 PM


A document generated at work has a column which
contains
both a date and time in each cell. I am trying to
build
an
IF statement that will look at the date only and
ignore
the time, then provided a specific value if it is the
current date and something else if it is not. When I
delete the time from the cell and just leave the
date,
the
IF statement works perfectly. When I leave the time
there
the IF statement sees it as a different value and
does
not
work right. Since I am not the author of this
report,
I
can not change it to place the time in a different
cell
from the date. Is there any way I can have the IF
statement ignore the time so that it just uses the
date?
Doug
..


.

.


.

.

After you clicked on Space in the second window, you should see a vertical
line appear between the date (6/14/04) and the time (17:00).
Did (do) you see this?

Then, after you click on Next, you should see the data displayed in two
columns, where the date is black and the time is white, and both have a
"General" label for the column format.
Did (do) you see this?

Then, in the same window, after you click on "Date", the format label over
the date column now changes to "MDY".
Did (do) you see this?

Finally, in the "Destination" box, enter a column address where you will
have two empty, contiguous columns, to accept the two new columns of data.
For example, just enter D1, if D1 and E1 are empty and available to accept
the number of rows that you originally selected for the TTC.

Then just Finish.

This should give you a column of "true" XL dates, and "true" XL times, which
can be verified by selecting one of the cells and seeing what's displayed in
the formula bar.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------

wrote in message
...
I tried doing a text to column convert, but the date and
time didn't separate into two columns.

I moved the data from one of the cells off to the side. I
selected the new cell with the data in it and clicked on
data and then text to columns. I selected delimited and
next and choose space as the delimiter, since there is a
space between the date and time. I formatted with date and
when I clicked on finish I didn't notice any changes, both
date and time remained in one cell.

Why didn't it separate into two columns and what can I do
to get it to work right?

Thanks for your help,
Doug

-----Original Message-----

You stated that:
"but I do have control of what I do with it once it is
pasted into
excel."

So, do a "TextToColumns", convert the imported TEXT data
to a true date and
time, placing the "converted data" in an out of the way
portion of the
sheet, and reference your formulas to this converted
(revised) (hidden) data
column.
--
HTH,

RD

----------------------------------------------------------
-----------------
Please keep all correspondence within the NewsGroup, so
all may benefit !
----------------------------------------------------------
-----------------
wrote in message
...
Biff,
The data is copied from a report and then pasted into
excel. I don't have control over how the report is
formated, but I do have control of what I do with it
once
it is pasted into excel.

What can I do so that it will recognize the date and
ignore the time, without actually deleting the time?
Thanks,
Doug

-----Original Message-----
The format category is general.

If you have 6/24/04 17:00 entered in a cell and
formatted
as general then it is not being recognized as a date
and
is therefor text. If you enter a recognized date string
such as 6/24/04 17:00, Excel will automatically format
it
as a date/time. If you then change that format to a
general format, the new displayed value in that cell
will
be 38162.70833.

38162 is the numeric value of the date. It's the 38162
nd
day since 1/1/1900.

..70833 is the numeric value of the time. It's the
fractional part of a day (24 hr).

Is this "date string" manually entered? Is it
copy/pasted
from a different source? Copy/pasting is notorious for
including unseen characters which can cause just the
problem your describing.

Biff

-----Original Message-----
Does anyone have a solution?

Subject: If statement
From: "Doug"
Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the value
is
Text and can't
be coerced into a date/time.

What is the exact format of the date/time?


Thanks for your help JE,
When I use INT I get a #VALUE response whether or not
the
date is current. This is what I used: =IF(INT(M35)
=TODAY
(),0,5). M35 is the cell that contains both the date
and
time in the following format: 6/24/04 17:00.

What can I do to get a response of 0 when the date is
current and 5 when it is not.
Doug



Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base date,
and
times as
fractional days (so 3:00 = 0.125). So to ignore times,
use
INT():

=IF(INT(A1)=TODAY(),"It's Today!", "It's not
Today.")

In article ,
"Doug" wrote:



Subject: If statement
From: "Doug"
Sent: 6/24/2004
9:10:43 PM


A document generated at work has a column which
contains
both a date and time in each cell. I am trying to
build
an
IF statement that will look at the date only and
ignore
the time, then provided a specific value if it is the
current date and something else if it is not. When I
delete the time from the cell and just leave the date,
the
IF statement works perfectly. When I leave the time
there
the IF statement sees it as a different value and does
not
work right. Since I am not the author of this report,
I
can not change it to place the time in a different
cell
from the date. Is there any way I can have the IF
statement ignore the time so that it just uses the
date?
Doug
..


.

.


.

RD,
Thanks for your help. When I selected delimiter, it would
not separate the date and time into two columns, however,
when I selected fixed width it did allow me to separate
date and time. I clicked between the date and time and the
vertical line appeared.

When I selected date the format label changed to MDY and
when I clicked on finish - wa la - the data was separated
in two columns.

When you use the terms "true" XL dates, and "true" XL
times, do you mean that the formatting is neither text or
general, but rather a numerical code representing the date
and time?

RD, again thanks for your help. This will allow me to be
more efficient when working with spreadsheets.
Doug

-----Original Message-----
After you clicked on Space in the second window, you
should see a vertical
line appear between the date (6/14/04) and the time
(17:00).
Did (do) you see this?

Then, after you click on Next, you should see the data
displayed in two
columns, where the date is black and the time is white,
and both have a
"General" label for the column format.
Did (do) you see this?

Then, in the same window, after you click on "Date", the
format label over
the date column now changes to "MDY".
Did (do) you see this?

Finally, in the "Destination" box, enter a column address
where you will
have two empty, contiguous columns, to accept the two new
columns of data.
For example, just enter D1, if D1 and E1 are empty and
available to accept
the number of rows that you originally selected for the
TTC.

Then just Finish.

This should give you a column of "true" XL dates,
and "true" XL times, which
can be verified by selecting one of the cells and seeing
what's displayed in
the formula bar.
--
HTH,

RD

----------------------------------------------------------
-----------------
Please keep all correspondence within the NewsGroup, so
all may benefit !
----------------------------------------------------------
-----------------

wrote in message
...
I tried doing a text to column convert, but the date and
time didn't separate into two columns.

I moved the data from one of the cells off to the side.
I
selected the new cell with the data in it and clicked on
data and then text to columns. I selected delimited and
next and choose space as the delimiter, since there is a
space between the date and time. I formatted with date
and
when I clicked on finish I didn't notice any changes,
both
date and time remained in one cell.

Why didn't it separate into two columns and what can I
do
to get it to work right?

Thanks for your help,
Doug

-----Original Message-----

You stated that:
"but I do have control of what I do with it once it
is
pasted into
excel."

So, do a "TextToColumns", convert the imported TEXT
data
to a true date and
time, placing the "converted data" in an out of the way
portion of the
sheet, and reference your formulas to this converted
(revised) (hidden) data
column.
--
HTH,

RD

-------------------------------------------------------
---
-----------------
Please keep all correspondence within the NewsGroup, so
all may benefit !
-------------------------------------------------------
---
-----------------
wrote in message
...
Biff,
The data is copied from a report and then pasted into
excel. I don't have control over how the report is
formated, but I do have control of what I do with it
once
it is pasted into excel.

What can I do so that it will recognize the date and
ignore the time, without actually deleting the time?
Thanks,
Doug

-----Original Message-----
The format category is general.

If you have 6/24/04 17:00 entered in a cell and
formatted
as general then it is not being recognized as a date
and
is therefor text. If you enter a recognized date
string
such as 6/24/04 17:00, Excel will automatically
format
it
as a date/time. If you then change that format to a
general format, the new displayed value in that cell
will
be 38162.70833.

38162 is the numeric value of the date. It's the
38162
nd
day since 1/1/1900.

..70833 is the numeric value of the time. It's the
fractional part of a day (24 hr).

Is this "date string" manually entered? Is it
copy/pasted
from a different source? Copy/pasting is notorious
for
including unseen characters which can cause just the
problem your describing.

Biff

-----Original Message-----
Does anyone have a solution?

Subject: If statement
From: "Doug"
Sent:
6/25/2004
5:42:46 AM


The format category is general.


Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the
value
is
Text and can't
be coerced into a date/time.

What is the exact format of the date/time?


Thanks for your help JE,
When I use INT I get a #VALUE response whether or
not
the
date is current. This is what I used: =IF(INT(M35)
=TODAY
(),0,5). M35 is the cell that contains both the
date
and
time in the following format: 6/24/04 17:00.

What can I do to get a response of 0 when the date
is
current and 5 when it is not.
Doug



Subject: If statement
From: "JE McGimpsey"
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base
date,
and
times as
fractional days (so 3:00 = 0.125). So to ignore
times,
use
INT():

=IF(INT(A1)=TODAY(),"It's Today!", "It's not
Today.")

In article 210e101c45a6a$624f1680
,
"Doug"
wrote:



Subject: If statement
From: "Doug"
Sent:
6/24/2004
9:10:43 PM


A document generated at work has a column which
contains
both a date and time in each cell. I am trying to
build
an
IF statement that will look at the date only and
ignore
the time, then provided a specific value if it is
the
current date and something else if it is not. When
I
delete the time from the cell and just leave the
date,
the
IF statement works perfectly. When I leave the time
there
the IF statement sees it as a different value and
does
not
work right. Since I am not the author of this
report,
I
can not change it to place the time in a different
cell
from the date. Is there any way I can have the IF
statement ignore the time so that it just uses the
date?
Doug
..


.

.


.


.