Ron,

1. The data shows true and false. Both results are shown. Therefore both
data and text in colA

2. Data is downloaded from our IT database. (I have spoke to them several
times on this issues yet no resolve) It would be recorded at the time of the
transtaction there is a date/text stamp used. How it get thre I am not 100%
sure. Will try to find out. (I know this is the issue, but I need a work
around until this is fixed, if ever)

3. Configuration setting is correct. the day/month/year difference comes
form item 2. Not sure way again

Hope this is of some use


"Ron Rosenfeld" wrote:

On Tue, 6 Dec 2005 00:34:33 -0800, "MijC"
wrote:

Ron,
Please find below the data as shown in the download:
COLUMN A COLUMN B COLUMN C
11/12/2005 23:20 11/12/DN D
11/13/2005 00:08:03 11/12/DN D

In Row 1 the date is not text
In row 2 the date is tex
in column B I use the follow formula

=CONCATENATE(IF(HOUR(A1062)=7,DAY(A1062),DAY(A10 62)-1),"/",MONTH(A1062),"/",F1062,IF(OR(HOUR(A1062)<7,HOUR(A1062)=19),"N"," D"))

Column c show the shift that is working at that time. Shift time from 7am
to 7pm and 7pm to 7 am denoted by D (days) or N (nights)

Due to row 2 being text the above formula cannot be use.
I then use left() mid() right() functions.
this gives me issues with the day or night function. require further columns
for this.

Is there some way of determining which formula to use fom analysing using
the istext function ie.
use blak colum as you said for istext(a1)
if say b1 = true then use left(),mid() etc..
IF b1 = false then fourmula above.
I believe this will still give me an issue with the D or N function

thanks for the help


As I wrote in my previous post, you can always figure out if a date string is
TEXT or not by using the =ISTEXT(cell_ref) function. What happened when you
used that function on the cells in column A?

But I would use a different approach for consistency.

I would ensure that the data in Column A is processed as a true date, rather
than trying to pick it apart and figure out if it is text or not. You can then
use date functions which will simplify things quite a bit.

One of the potential problems with your formula is if the DAY happens to be the
first day of the month, and HOUR < 7 , then wouldn't you also need to change
your month to the previous month? And you couldn't just subtract '1' but you'd
also have to take into account the month before '1' is '12'.

Much simpler to use a single formula that uses date functions, such as:

=TEXT(DATE(YEAR(A1),MONTH(A1),DAY(A1)-1+(HOUR(A1)=7)),
"dd/mm/\"&F1& IF(OR(HOUR(A1)<7,HOUR(A1)=19),"\N","\D"))


So let's figure out why some of your data is text and why some of it is real
dates.

1. If you do =ISTEXT(cell_ref) where cell_ref are the cells in colA that you
think are and are not text, do you get the expected results or are they all
TRUE?

2. How does the data get into column A?

Is it manually entered?, the result of a formula?, copied from the web or an
HTML document?, etc???


3. Is the Day/Month/Year order the same as in your Windows/Control
Panel/Language and Regional Settings configuration?


--ron