If statement

  • Thread starter Thread starter Doug
  • Start date Start date
D

Doug

Does anyone have a solution?

Subject: Re: If statement
From: "Doug"
<[email protected]> Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: Re: If statement
From: "JE McGimpsey" <[email protected]>
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: Re: If statement
From: "JE McGimpsey" <[email protected]>
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.")

Subject: If statement
From: "Doug"
<[email protected]> 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
 
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
 
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.
 
OR -

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

Copy an MT cell, select the text date string, Paste
Special>Add>OK

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

Biff
 
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.
 
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
 
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
Special>Add>OK

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

---------------------------------------------------------
Click to expand...
- - format
it
Click to expand...
38162
nd
Click to expand...
value
is
Click to expand...
report,
I

.
Click to expand...
.
Click to expand...
 
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.
 
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 !
---------------------------------------------------------- -----------------

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
Click to expand...
is
pasted into
excel.">>

So, do a "TextToColumns", convert the imported TEXT
Click to expand...
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

-------------------------------------------------------
Click to expand...
---
-----------------
Please keep all correspondence within the NewsGroup, so all may benefit !
-------------------------------------------------------
Click to expand...
---
-----------------
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
Click to expand...
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
Click to expand...
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: Re: If statement
From: "Doug"
<[email protected]> Sent: 6/25/2004
5:42:46 AM


The format category is general.


Subject: Re: If statement
From: "JE McGimpsey" <[email protected]>
Sent: 6/24/2004 11:04:38 PM


That means that instead of an XL date/time, the
Click to expand...
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
Click to expand...
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: Re: If statement
From: "JE McGimpsey" <[email protected]>
Sent: 6/24/2004 9:40:16 PM


XL stores dates as integer offsets from a base
Click to expand...
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.")
Click to expand...
Click to expand...
Click to expand...
 
Back
Top