Datevalue prob- some data text some number

Kade

Active Member
Joined
Jul 20, 2002
Messages
258
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Kade said:
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

Active Member
Joined
Jul 20, 2002
Messages
258
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

Active Member
Joined
Jul 20, 2002
Messages
258

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Kade said:
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
ABCD
23833612/15/2004
Sheet5
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

Kade said:
...

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

Active Member
Joined
Jul 20, 2002
Messages
258
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
Kade said:
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)
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,159
Messages
5,768,525
Members
425,480
Latest member
br400821

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top