Datevalue prob- some data text some number

[Kade]

Hi all

I import data from an accounting package.
the date column is causing me grief.
some data comes across ok as date format
but then some comes across as text.(but looks like date)

So it looks ok till I need to sort on date

The changes in format appear random down the column

If i use datevalue in the adjacent column it converts the text ok but leaves me with "VALUE" in the cells next to a correct date cell

How can i end up with a single column just in dateform?

tia kd

 

[Aladin Akyurek]

=IF(N(A1),A1,--A1)

Format the formula cell as appropriate.

 

[Yogi Anand]

Hi all

I import data from an accounting package.
the date column is causing me grief.
some data comes across ok as date format
but then some comes across as text.(but looks like date)

So it looks ok till I need to sort on date

The changes in format appear random down the column

If i use datevalue in the adjacent column it converts the text ok but leaves me with "VALUE" in the cells next to a correct date cell

How can i end up with a single column just in dateform?

tia kd

Hi Kade:

In addition to contribution from Aladin, you may want to try the following ...

1. select all the cells with dates (mixed text and true dates)
2. then do DATA|text_to_columns, and then Finish

all entries selected would be converted to true dates.

 

[Kade]

Thanks Aladin,

I first thought it solved all I did not know the "N" function

But I now see the underlying problem is that the export process is directly linked to excel (internal button on the quickbooks program).

It is converting aussie date format to US ddmmyy -> mmddyy

so the underlying date number is coming across incorrect. It was the days that had months expressed greater than 12 that were coming across as text (it didn't recognise it is ddmm)

Maybe there is no solution as the day number is wrong to start with.

HOWEVER

the column straight after exporting visually looks ok example

entry 1 - displays 12/10/03 (so it looks ok 12 Oct in aus) but the underlying true date is Dec 10

entry 2 - displays 13/10/03 (sp it looks ok as 13 oct in aus) but it has come across as text as it did not recognise the data as date.


Only solution I can see is to copy and paste-special the displayed value as text, can this be done? (so removing the underlying date number)

ta td

 

[Kade]

Hi , I,ve bumped this cos I think I need to simplify what I think my problem now is.

I have a cell displaying a date as 12/15/04 (underlying date number 38336)

can I convert this to a text only cell (no underlying date number)?? so that it displays 12/15/04, text field

ta td

 

[Yogi Anand]

Hi , I,ve bumped this cos I think I need to simplify what I think my problem now is.

I have a cell displaying a date as 12/15/04 (underlying date number 38336)

can I convert this to a text only cell (no underlying date number)?? so that it displays 12/15/04, text field

ta td

Book1
	A	B	C	D
2		38336	12/15/2004
Sheet5

 

[Aladin Akyurek]

...

HOWEVER

the column straight after exporting visually looks ok example

entry 1 - displays 12/10/03 (so it looks ok 12 Oct in aus) but the underlying true date is Dec 10

entry 2 - displays 13/10/03 (sp it looks ok as 13 oct in aus) but it has come across as text as it did not recognise the data as date.

...

Just to make sure:

12/10/03 is text or number? Should be what: 12-Oct-03 or 10-Dec-03?

13/10/03 is text and should be: 13-Oct-03, right?

Can you give some more examples structured like this:

entry, TEXT, should be: day as number,-,monthname,-,year as number.

 

[Kade]

Thanks all,

I have resolved my problem by exporting the data as csv, raw and importing into excel rather than using the feature inbuilt in the program.

I gave up on the inbuilt quickbooks program (convert to excel) because although the Aus version dd/mm/yy worked within the program, the conversion process converted it to mm/dd/yyy but left the numbers in the same sequence so 12 October became 10 Dec

However if it had a date with a seeemingly month count higher than 12 then exported it as text.

so the data when exported would do this


Actual
12/10/2003 (dd/mm)
37906 True date number

But come across visually as
12/10/2003 (mm/dd) so it looks ok
37965 Wrong date number for 12 Dec

SO in the conversion it changes 12 Oct to 10 Dec underlying number

Obviosly reformatting cells will not change the erronious underlying number, and the TEXT function however you jig it will still be working of the incorrect date number

Once a report wxceeds 12 in the first part it doesn't acknowledge it as a date and converts as text

13/10/2003 (dd/mm) 13/10/2003 (AS TEXT ONLY)

So coming across as text was ok I could convert that (thanks Yogi),
BUT, when the entries with monthcount 12 and below it converted it to the US underlying number.

I am confident it has nothing to do with my set up etc. But just a quirk in the auto convert to excel feature with in the quickbooks program.

No problem for me now as I just export differently but a curious problem.

Put simply can a cell with (December,10)37965 be converted to
(October,12)37906

ta kd

 

[Aladin Akyurek]

Thanks all,

I have resolved my problem by exporting the data as csv, raw and importing into excel rather than using the feature inbuilt in the program.

...

That sounds good. I'm just curious whether this would solve the issue...

=IF(N(A1),DATE(YEAR(A1),DAY(A1),MONTH(A1)),--A1)

 

[Kade]

he he he he, Aladin you are amazing!

ta kd