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(A106 2)-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

"Ron Rosenfeld" wrote:

On Mon, 5 Dec 2005 01:58:02 -0800, "MijC"
wrote:

Wonder if anyone can help.
In column A there is a date and time function in the format as follows :
dd/mm/yy hh:mm:ss most of the time.
When this is downloaded it is easy to manuplite. But some times it show as
above but as a text string or sometimes the dd/mm is switched to mm/dd.
Does anyone know the work rounds these formulas?

I use the dd/mm and Hr function to complie reports from column A but usually
spend hours using mid() left() function due to the date change.
I have tried formating the cell but this does not work?

Help
MIJC

What is the function in Column A? (Excel has many date and time functions).

What are the inputs to the Column A function and what do you obtain for outputs
or display?

When you say the dd/mm is switched to mm/dd, is that in the downloaded data or
is that after you have performed some Excel operation on the downloaded data?

Are you perhaps not using the word "function" as it is used in Excel, but
rather merely mean that you are downloading date/time strings from the "web"
into column A? If that is the case, it is likely that all of the strings in
Column A are, in fact, text strings. This can be easily determined by using
the formula =ISTEXT(A1) in some unused column and copying it down as far as
necessary.


--ron