Dates in Excel

  • Thread starter Thread starter Nuexcelts
  • Start date Start date
N

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
 
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
 
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.
 
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
 
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
 
Back
Top