Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
Default Day & Month reversed when importing text

Hopefully my illustration describes why I'm baffled:

https://www.dropbox.com/s/sb8wkiypq9...le-01.jpg?dl=0

The problem must arise in this first section, before calling other three
macros which further manipulate the imported text.

Sub ImportFile_Copy()
'
' ImportFile_Copy Macro
' Imports the TXT file _
' C:\Users\terry\Dropbox\Excel+VBA (Sundry)\TEMP-VariableList.txt

Workbooks.OpenText Filename:= _
"C:\Users\terry\Dropbox\Excel+VBA
(Sundry)\TEMP-VariableList.txt", Origin:=xlMSDOS, _
StartRow:=1, DataType:=xlDelimited,
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False,
Comma:=False _
, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1),
Array(2, 1)), _
TrailingMinusNumbers:=True

Windows("TEMP-VariableList.txt").Activate

' Change size & position
Application.Left = 782.75
Application.Top = 1.25
Application.Width = 658.5
Application.Height = 879.5
' Set col A & B width to 26 & 88 and align both left
Columns("A:A").ColumnWidth = 26
Columns("A:A").HorizontalAlignment = xlLeft
'Columns("B:B").Select
Columns("B:B").ColumnWidth = 26
'Selection.ColumnWidth = 88
Columns("B:B").HorizontalAlignment = xlLeft
etc

It's almost as if Excel's Import Wizard totally ignores regional
settings and uses the USA standard.

And I'm almost certain this was working smoothly a few days ago... Could
it be yet another Win 10 WU issue?

Terry, East Grinstead, UK
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Day & Month reversed when importing text

Terry,
This has been a contentious issue since Vista when the default date format
switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited text
files is to 'dump' the file contents into an array so I can setup number
formats to suit the template the data will get 'dumped' into.

Unfortunately, Excel uses default formats when importing csv/tsv/txt files and
so inaccurate data results. What's important about importing from these sources
is that they are created as expected by the host template. This breaks when the
file has been created in XP (for example) because date formats don't align with
Vista & later OSs.

Possible Solutions:
Format date columns before importing the data via Excel's Import feature;
Read the data into an array via VBA, then either...
format the date data;
format the target date cols;
..before dumping the data into the sheet/template.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
Default Day & Month reversed when importing text

GS wrote:

Terry,
This has been a contentious issue since Vista when the default date format
switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited text
files is to 'dump' the file contents into an array so I can setup number
formats to suit the template the data will get 'dumped' into.

Unfortunately, Excel uses default formats when importing csv/tsv/txt files and
so inaccurate data results. What's important about importing from these sources
is that they are created as expected by the host template. This breaks when the
file has been created in XP (for example) because date formats don't align with
Vista & later OSs.

Possible Solutions:
Format date columns before importing the data via Excel's Import feature;
Read the data into an array via VBA, then either...
format the date data;
format the target date cols;
..before dumping the data into the sheet/template.


Thanks Garry. I've just been reading many posts about this and related
problems. Those and now your detailed reply prompt me to the conclusion
that it's a dog's dinner and really too much like hard work to fix
confidently.

The import wizard simply isn't quite smart enough IMO. A text entry of
12/8/2017 ends up as 8th December yet 13/8/2017 correctly becomes 13th
August.

I may settle for popping up a message at the appropriate point,
reminding the user (me) to correct a reversed date.

Terry, East Grinstead, UK
  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Day & Month reversed when importing text

GS wrote:

Terry,
This has been a contentious issue since Vista when the default date format
switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited
text files is to 'dump' the file contents into an array so I can setup
number formats to suit the template the data will get 'dumped' into.

Unfortunately, Excel uses default formats when importing csv/tsv/txt files
and so inaccurate data results. What's important about importing from these
sources is that they are created as expected by the host template. This
breaks when the file has been created in XP (for example) because date
formats don't align with Vista & later OSs.

Possible Solutions:
Format date columns before importing the data via Excel's Import feature;
Read the data into an array via VBA, then either...
format the date data;
format the target date cols;
..before dumping the data into the sheet/template.


Thanks Garry. I've just been reading many posts about this and related
problems. Those and now your detailed reply prompt me to the conclusion
that it's a dog's dinner and really too much like hard work to fix
confidently.

The import wizard simply isn't quite smart enough IMO. A text entry of
12/8/2017 ends up as 8th December yet 13/8/2017 correctly becomes 13th
August.


The conversion seems to be based on the 1st part (12/) in that if it's over 12
it's taken as a day, otherwise a month. My approach works consistently reliable
to the various tasks I use it with. Answer seems to lie in the format of target
date cols BEFORE the data is entered!

I may settle for popping up a message at the appropriate point,
reminding the user (me) to correct a reversed date.

Terry, East Grinstead, UK


--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
Default Day & Month reversed when importing text

GS wrote:

GS wrote:

Terry,
This has been a contentious issue since Vista when the default date format
switched from mm/dd/yyyy to dd/mm/yyyy. My approach to importing delimited
text files is to 'dump' the file contents into an array so I can setup
number formats to suit the template the data will get 'dumped' into.

Unfortunately, Excel uses default formats when importing csv/tsv/txt files
and so inaccurate data results. What's important about importing from these
sources is that they are created as expected by the host template. This
breaks when the file has been created in XP (for example) because date
formats don't align with Vista & later OSs.

Possible Solutions:
Format date columns before importing the data via Excel's Import feature;
Read the data into an array via VBA, then either...
format the date data;
format the target date cols;
..before dumping the data into the sheet/template.


Thanks Garry. I've just been reading many posts about this and related
problems. Those and now your detailed reply prompt me to the conclusion
that it's a dog's dinner and really too much like hard work to fix
confidently.

The import wizard simply isn't quite smart enough IMO. A text entry of
12/8/2017 ends up as 8th December yet 13/8/2017 correctly becomes 13th
August.


The conversion seems to be based on the 1st part (12/) in that if it's over 12
it's taken as a day, otherwise a month. My approach works consistently reliable
to the various tasks I use it with. Answer seems to lie in the format of target
date cols BEFORE the data is entered!

I may settle for popping up a message at the appropriate point,
reminding the user (me) to correct a reversed date.

Terry, East Grinstead, UK


I think the Import Wizard's design should allow you to enable BOTH
General and one of those DMY options. So data that it (cleverly) decides
is meant to be a date (rather than text) should then be formatted as
specified. Instead of unintelligently applying the USA default. Or, at
the very least, in ambiguous cases like the one under discussion, it
should apply the user's OS regional setting. As it's bright enough to
decide that 13/8/2017 is 13th August, a few more lines of code should
enable it to convert 12/8/2017 (on my UK PC) to 12th August, not 8th
December.

However, before dozing off last night I had a flash of inspiration.
Should have thought of it before. In my annotated screenshot, B5
contains the track name. That always starts with eight digits
representing the date of the walk/hike/trip.

I have to leave my PC for a couple of hours now for Saturday chores. My
intention after returning is to try writing another VBA macro after the
import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it
with Macro Express Pro.

Terry, East Grinstead, UK


  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Day & Month reversed when importing text

I have to leave my PC for a couple of hours now for Saturday chores. My
intention after returning is to try writing another VBA macro after the
import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it
with Macro Express Pro.

Terry, East Grinstead, UK


Did you try to set the format to show the short name of the month instead of
its numeric value? What I see in B6 and the format you've chosen makes sense
for OSs after XP; -it's interpreting day=8, month=12. In XP that would be the
other way around; month=8, day=12.

You cou make a macro to parse the left side of B5 and build the date format
from its parts:

Option Explicit

Sub FixDate()
Dim m, d, y, s
s = Range("B5").Value
y = Mid(s, 1, 4): m = Mid(s, 5, 2): d = Mid(s, 7, 2)
With Range("B6")
.NumberFormat = "mmm dd, yyyy": .Value = m & "/" & d & "/" & y
End With
End Sub

Note that I use the above format for reliable consistency with date cells so
there's no ambiguity for month/day. (IOW, eliminate the cause and you'll always
have clarity!) Edit that to your liking.

In financial transaction sheets where the calendar year is a fiscal year, I
omit the year in the TransactionDate field so "mmm-dd" is all that displays.

If you don't want macros in the project file, put this is PERSONAL.xls.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
Default Day & Month reversed when importing text

GS wrote:

I have to leave my PC for a couple of hours now for Saturday chores. My
intention after returning is to try writing another VBA macro after the
import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it
with Macro Express Pro.

Terry, East Grinstead, UK


Did you try to set the format to show the short name of the month instead of
its numeric value? What I see in B6 and the format you've chosen makes sense
for OSs after XP; -it's interpreting day=8, month=12. In XP that would be the
other way around; month=8, day=12.

You cou make a macro to parse the left side of B5 and build the date format
from its parts:

Option Explicit

Sub FixDate()
Dim m, d, y, s
s = Range("B5").Value
y = Mid(s, 1, 4): m = Mid(s, 5, 2): d = Mid(s, 7, 2)
With Range("B6")
.NumberFormat = "mmm dd, yyyy": .Value = m & "/" & d & "/" & y
End With
End Sub

Note that I use the above format for reliable consistency with date cells so
there's no ambiguity for month/day. (IOW, eliminate the cause and you'll always
have clarity!) Edit that to your liking.

In financial transaction sheets where the calendar year is a fiscal year, I
omit the year in the TransactionDate field so "mmm-dd" is all that displays.

If you don't want macros in the project file, put this is PERSONAL.xls.


Thanks Garry, looks good, will try it shortly.

I have just been doing the same thing with this formula for extracting
d/m/y from Track name's first 8 characters:

=CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2))

I was baffled why that failed - but then realised that I hadn't
carefully thought it through. I placed it into B6 but it's getting
over-written by the import.

I'll provide more detail as I anticipate further discussion!

1. For all my walks/hikes/trips I get data automatically from a GPX
file. I do that using MX Pro and three mapping programs (GPS Utility,
Memory Map, Google Earth). Altogether over 50 items (text, numbers,
times, dates). All are variables in MX Pro.

2. To get these into a worksheet I was at first using MX Pro to
successively paste each into 50 cells. Kludgy and glacially slow. So now
I first quickly build and save a text file (with MX Pro) with 50 entries
of two columns. For example, the first 13 are destined for cols B and C
of my worksheet. All, of course, are at this stage simply text. In
particular, the line 6 entry contains
Walk date[TAB]12/8/17

A point I've not mentioned before is that I have an alternative way of
preparing that Walk Date for the text file, namely
=TEXT("12/8/2017", "dddd dd mmmm yyy")
If that reached B6 of my worksheet as it stands, that would display
Saturday 12 August 2017. Very close to the result I want, which is
Saturday 12th August 2017.

3. I then use the VBA macro we've been discussing to import that text
file as a workbook TEMP-VariableList.txt. Which is where confusion
starts, due to the import wizard's shortcomings.

4. As you see from the lower section of my worksheet
https://www.dropbox.com/s/3wv0z1d12o...-Full.jpg?dl=0
B27 & B28 are for use in another macro, saving this sort of image:
https://www.dropbox.com/s/50o092d9ul...ample.jpg?dl=0

Terry, East Grinstead, UK
  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 192
Default Day & Month reversed when importing text

GS wrote:

I have to leave my PC for a couple of hours now for Saturday chores. My
intention after returning is to try writing another VBA macro after the
import to convert 20170812 to 12/8/2017 in B6. Failing that I'll do it
with Macro Express Pro.

Terry, East Grinstead, UK


Did you try to set the format to show the short name of the month instead of
its numeric value? What I see in B6 and the format you've chosen makes sense
for OSs after XP; -it's interpreting day=8, month=12. In XP that would be the
other way around; month=8, day=12.

You cou make a macro to parse the left side of B5 and build the date format
from its parts:

Option Explicit

Sub FixDate()
Dim m, d, y, s
s = Range("B5").Value
y = Mid(s, 1, 4): m = Mid(s, 5, 2): d = Mid(s, 7, 2)
With Range("B6")
.NumberFormat = "mmm dd, yyyy": .Value = m & "/" & d & "/" & y
End With
End Sub

Note that I use the above format for reliable consistency with date cells so
there's no ambiguity for month/day. (IOW, eliminate the cause and you'll always
have clarity!) Edit that to your liking.

In financial transaction sheets where the calendar year is a fiscal year, I
omit the year in the TransactionDate field so "mmm-dd" is all that displays.

If you don't want macros in the project file, put this is PERSONAL.xls.


Hi Garry,

Your macro works fine here, many thanks. I've currently applied it to K6
in the DD/MM/YY format.

But meanwhile I'd implemented an approach using a formula.

With the date 12/08/17 in B6, the following formula I obtained elsewhere
a couple of weeks ago

=TEXT(B6,"dddd")&"
"&DAY(B6)&IF(OR(DAY(B6)={1,2,3,21,22,23,31}),CHOOS E(1*RIGHT(DAY(B6),1),"st
","nd ","rd "),"th ")&TEXT(B6,"mmmm yyyy")

clevely displayed it complete with ordinals, i.e:
Saturday 12th August 2017

So I've now edited that formula and it can be applied to B5 to give the
same result. But it was a VERY tedious and error-prone task. I was
rather amazed that it worked! Is there a neater way to do it please?

=TEXT(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)),"dddd")&"
"&DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)))&IF(OR(DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)))={1,2,3,21,22,23,31}),CHOOSE(1*RIGH T(DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2))),1),"st
","nd ","rd "),"th
")&TEXT(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)),"mmmm yyyy")

Finally (for now!) is there a general way to code a VBA macro to insert
that or any formula? Both seem to have have pros and cons so I'd value
the choice.

Terry, East Grinstead, UK
  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Day & Month reversed when importing text

So I've now edited that formula and it can be applied to B5 to give the
same result. But it was a VERY tedious and error-prone task. I was
rather amazed that it worked! Is there a neater way to do it please?

=TEXT(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)),"dddd")&"
"&DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)))&IF(OR(DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)))={1,2,3,21,22,23,31}),CHOOSE(1*RIGH T(DAY(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2))),1),"st
","nd ","rd "),"th
")&TEXT(CONCAT(MID(B5,7,2),"/",MID(B5,5,2),"/",MID(B5,3,2)),"mmmm yyyy")


Claus is the "formula wizard" here but I can have a go at it. First thing I'd
lose is the CONCAT() function in favor of "&" for brevity as well as clarity.

Finally (for now!) is there a general way to code a VBA macro to insert
that or any formula? Both seem to have have pros and cons so I'd value
the choice.


Having VBA insert the formula adds overhead to the workbook/sheet Calculation
method. Having VBA insert the result as a Value is more efficient, IMO.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 3,872
Default Day & Month reversed when importing text

Hi Terry,

Am Sat, 17 Feb 2018 22:46:32 +0000 schrieb Terry Pinnell:

=TEXT(B6,"dddd")&"
"&DAY(B6)&IF(OR(DAY(B6)={1,2,3,21,22,23,31}),CHOOS E(1*RIGHT(DAY(B6),1),"st
","nd ","rd "),"th ")&TEXT(B6,"mmmm yyyy")


try:
=TEXT(B6,"dddd ")&DAY(B6)&IFERROR(VLOOKUP(DAY(B6),{1,"st";2,"nd"; 3,"rd";21,"st";22,"nd";23,"rd";31,"st"},2,0),"th") &TEXT(B6," MMMM YYYY")


Regards
Claus B.
--
Windows10
Office 2016


  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 1,182
Default Day & Month reversed when importing text

How about a UDF compromise? This gives you the VBA to use in code OR as a cell
formula as...

=FixDate2(B5)

Public Function FixDate2(rng As Range)
Dim s, m, d, y, v()
s = Left$(rng.Value, 8): ReDim v(2)
v(2) = Mid(s, 1, 4): v(1) = Right$(s, 2): v(0) = Mid(s, 5, 2)
s = Format(Join(v, "/"), "dddd dd mmmm, yyyy"): d = Split(s, " ")
Select Case Day(CDate(Join(v, "/")))
Case 1, 21, 31: d(1) = d(1) & "st"
Case 2, 22: d(1) = d(1) & "nd"
Case 3, 23: d(1) = d(1) & "rd"
Case Else: d(1) = d(1) & "th"
End Select
FixDate2 = Join(d, " ")
End Function

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 7
Default Day & Month reversed when importing text

Thanks both. Been away for a while but aim to try both of those suggestions ASAP.

Terry, East Grinstead, UK
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
convert text month to numeric month salgud Excel Programming 10 April 1st 09 10:23 PM
EXCEL: How to scan text reversed (like ACCESS: InStrRev)? 4mula_freak Excel Worksheet Functions 8 August 22nd 07 08:05 AM
EXCEL: How to scan text reversed (like ACCESS: InStrRev)? 4mula_freak[_2_] Excel Worksheet Functions 3 August 21st 07 01:19 PM
Converting Month Number to Month Text Abbreviation Bob Excel Worksheet Functions 10 May 12th 07 04:11 AM
How can I show the Month in Text with the =month command Ovid New Users to Excel 2 October 24th 06 02:50 AM


All times are GMT +1. The time now is 06:08 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"