Date function/Text issue

[MijC]

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

 

[Ron Rosenfeld]

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

 

[MijC]

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(A1062)-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]

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(A1062)-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

 

[MijC]

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]

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)

If that is the case, I think the work around should be to convert the TEXT
strings to real dates.

Let's try a few things.

1. With one of the strings were ISTEXT(cell_ref) = TRUE
1. Copy/Paste into your response the contents of the cell.
2. What is the result of the formula =LEN(cell_ref) ??
(This should tell us if there are any non-printing characters).

2. What happens if you prepend a double unary (--) to the TEXT strings? (i.e.

=--11/13/2005 00:08:03

That might force the string to become a true date/time (number) and, if
it works, we can use a much simpler procedure to apply it to everything. If
that does not work, then the results of procedure 1 above will become
important.


--ron

 

[MijC]

Ron,

1. For true shows the following info : 04/01/1900 00:00
actual date in COL A 11/13/2005 00:08:03

2. this gives me an error message.

Regards
Jim

 

[Ron Rosenfeld]

1. For true shows the following info : 04/01/1900 00:00
actual date in COL A 11/13/2005 00:08:03

What about =LEN(An) ??


--ron

 

[Ron Rosenfeld]

What about =LEN(An) ??


--ron

Never mind. I see that you mean that LEN(An) shows 04/01/1900 00:00.

I will get back to you.
--ron

 

[Ron Rosenfeld]

What about =LEN(An) ??


--ron

LEN(A1) where A1 contains 11/13/2005 00:08:03 being equal to 04/01/1900
doesn't make sense to me as that translates to 92; and that seems to be to many
non-printing characters for something that displays as a date/time string.

Try this formula in a column adjacent to your date time strings. The formula
needs to be **array-entered** which means after typing/pasting it in, you need
to hold down <ctrl><shift> while hitting <enter>. If you did it properly, in
the function bar you will see that Excel has placed braces {...} around the
formula.

The formula should translate the date/time text strings to a true date.


--ron

 

[Ron Rosenfeld]

LEN(A1) where A1 contains 11/13/2005 00:08:03 being equal to 04/01/1900
doesn't make sense to me as that translates to 92; and that seems to be to many
non-printing characters for something that displays as a date/time string.

Try this formula in a column adjacent to your date time strings. The formula
needs to be **array-entered** which means after typing/pasting it in, you need
to hold down <ctrl><shift> while hitting <enter>. If you did it properly, in
the function bar you will see that Excel has placed braces {...} around the
formula.

The formula should translate the date/time text strings to a true date.


--ron

Here is the formula:

=IF(ISERR(-A1),--MID(A1,SEARCH("??/",A1),LEN(A1)
+2-SEARCH("??/",A1)-MATCH(TRUE,ISNUMBER(
-MID(A1,LEN(A1)+1-ROW(INDIRECT("1:"&
LEN(A1))),1)),0)),A1)


--ron