Dates in Excel

[Nuexcelts]

My accounts software, Quickbooks, exports data to Excel but it really
cocks up on dates. In Ireland, I enter dates as dd/mm/yyyy. In the
exported data the actual digits stay in the same order but are taken
to be USA style mm/dd/yyyy.

So 01/01/2004 is 1st Jan 2004 in Quickbooks, but exported as and
understood by Excel as Jan 1st 2004.
02/01/2004 is 2nd Jan 2004 in Quickbooks, but exported as and
understood by Excel as Feb 1st 2004.
13/01/2004 is 13th Jan 2004 in Quickbooks, but just a text string in
Excel because no 13th month exists.
At present I save the exported file in Excel as a .TXT (Tab Delimited)
file, close it, then re-import it to Excel. At step 3 of the Import
Wizard, I format the column containing the dates as type DMY. Then all
is well.
I have tried to automate changing the original excel file data but not
sucessfully to date(sic). Dates from the 13th of the month can be
manipulated easily enough by extracting the =LEFT 2 digit string, the
=MID 2 digit string and the 4 digit =RIGHT string. The underlying
value(# days since 31/12/1899) in dates from the 1st to the 12th of
the month, that Excel HAS recognised as legitimate dates, doesn't lend
itself to 'regular' manipulation and automated adjustment.
I would love to solve this puzzle but I need some assistance. Any
pointers?
Regards
Mick

 

[Lady Layla]

Make sure you have the correct regional settings set for your system

Go to your Windows Control Panel, select REgional settings, select the correct
setting (english UK I think) click apply and ok

try import again

: My accounts software, Quickbooks, exports data to Excel but it really
: cocks up on dates. In Ireland, I enter dates as dd/mm/yyyy. In the
: exported data the actual digits stay in the same order but are taken
: to be USA style mm/dd/yyyy.
:
: So 01/01/2004 is 1st Jan 2004 in Quickbooks, but exported as and
: understood by Excel as Jan 1st 2004.
: 02/01/2004 is 2nd Jan 2004 in Quickbooks, but exported as and
: understood by Excel as Feb 1st 2004.
: 13/01/2004 is 13th Jan 2004 in Quickbooks, but just a text string in
: Excel because no 13th month exists.
: At present I save the exported file in Excel as a .TXT (Tab Delimited)
: file, close it, then re-import it to Excel. At step 3 of the Import
: Wizard, I format the column containing the dates as type DMY. Then all
: is well.
: I have tried to automate changing the original excel file data but not
: sucessfully to date(sic). Dates from the 13th of the month can be
: manipulated easily enough by extracting the =LEFT 2 digit string, the
: =MID 2 digit string and the 4 digit =RIGHT string. The underlying
: value(# days since 31/12/1899) in dates from the 1st to the 12th of
: the month, that Excel HAS recognised as legitimate dates, doesn't lend
: itself to 'regular' manipulation and automated adjustment.
: I would love to solve this puzzle but I need some assistance. Any
: pointers?
: Regards
: Mick

 

[Dave Peterson]

I like Layla's advice, but I think I'd do one more import manually (specifying
dmy). But I'd turn on the macro recorder when I did it.

Then I'd just run that macro and be done with it. (As long as you keep that
extension .txt!)

In fact, I think I'd keep recording the macro so that I can add
borders/headers/filters/print setup/etc. Then I wouldn't have to do that each
time.

Record the macro from a new workbook. After you've finished debugging (there's
always debugging!), you can save that macro workbook.

Whenever you want to import that type *.txt file, just open that workbook and do
Tools|macro|macros... and run it.

 

[Nuexcelts]

Thanks for the suggestion, but been there done that...
That is where the problem originates...
I am as you say in the English UK regional settings (Ireland to be
more precise). However Quickbooks imports and exports in its home
format. If I export to Quickbooks from Excel I can reformat my
(dd/mm/yy) dates to the only format that Quickbooks will understand
(mm/dd/yy). (Not quite true because I can directly enter data in
Quickbooks as dd/mm/yyyy and it "understands". But when exporting from
Quickbooks it can't be tweeked to change. I know I could temporarily
change my settings to USA style just for thee import but..... Maybe
I'll try it just for the hell of it...
Mick

 

[Nuexcelts]

Further to your suggestion on Regional Settings I did change mine to
English (united States) and Short Date mm/dd/yyyy...and the export
works a dream. I can even stay at English (United States) and change
the Short Date format to dd/mm/yyyy and it reproduced my messed up
export. Unfortunately changing to English (United States) requires a
reboot and I don't have the Short Date option of mm/dd/yyyy in my
English (Ireland) settings. Hmmmmmmmm! Back to the drawing board......

Mick