Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > microsoft.public.excel.programming > #110602 > unrolled thread
| Started by | jan120253@gmail.com |
|---|---|
| First post | 2018-05-04 00:31 -0700 |
| Last post | 2018-05-07 22:03 -0700 |
| Articles | 20 on this page of 60 — 6 participants |
Back to article view | Back to microsoft.public.excel.programming
Check how date is entered jan120253@gmail.com - 2018-05-04 00:31 -0700
Re: Check how date is entered dpb <none@none.net> - 2018-05-04 07:46 -0500
Re: Check how date is entered "Auric__" <not.my.real@email.address> - 2018-05-04 15:55 +0000
Re: Check how date is entered dpb <none@none.net> - 2018-05-04 12:43 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-04 17:37 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-04 18:49 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-04 21:42 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-04 23:46 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-05 04:21 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-05 07:21 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-05 09:50 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-05 09:05 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-05 10:33 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-05 10:57 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-05 20:51 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-05 11:32 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-05 21:19 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-06 07:46 -0500
Re: Check how date is entered dpb <none@none.net> - 2018-05-06 09:37 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-06 20:37 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-06 19:53 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-06 21:03 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 00:06 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 03:14 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 07:45 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 13:06 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 13:53 -0500
Re: Check how date is entered malone <malone@nospam.net.nz> - 2018-05-08 08:19 +1200
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 15:35 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 17:38 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 17:26 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 00:19 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 01:31 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 07:45 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 09:45 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 14:34 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 14:09 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 17:06 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 13:48 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 15:02 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 14:59 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 17:04 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 18:53 -0500
Re: Check how date is entered dpb <none@none.net> - 2018-05-08 21:12 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 23:51 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-09 07:20 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-09 11:32 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-09 11:35 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-09 15:03 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-09 15:18 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 13:34 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 13:44 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 17:40 -0400
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 17:50 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-08 00:02 -0400
Re: Check how date is entered jan120253@gmail.com - 2018-05-07 01:45 -0700
Re: Check how date is entered jan120253@gmail.com - 2018-05-07 01:52 -0700
Re: Check how date is entered dpb <none@none.net> - 2018-05-07 08:16 -0500
Re: Check how date is entered GS <gs@v.invalid> - 2018-05-07 13:10 -0400
Re: Check how date is entered Ramon-México <cesaramon@gmail.com> - 2018-05-07 22:03 -0700
Page 2 of 3 — ← Prev page 1 [2] 3 Next page →
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-06 19:53 -0500 |
| Message-ID | <pco82m$f26$1@gioia.aioe.org> |
| In reply to | #110621 |
On 5/6/2018 7:37 PM, GS wrote: ... > So docs done on pre-Vista systems would still show correct dates when > opened in the later OSs because regardless of how the system format > displays, the DateSerial dictates the actual date value! That's ok for stuff already _in_ MS product; what about the question of importing data from external systems that may have different encoding between them? Looks like that's broken to me... --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-06 21:03 -0400 |
| Message-ID | <pco8m0$ced$1@dont-email.me> |
| In reply to | #110622 |
> On 5/6/2018 7:37 PM, GS wrote: > ... > >> So docs done on pre-Vista systems would still show correct dates when >> opened in the later OSs because regardless of how the system format >> displays, the DateSerial dictates the actual date value! > > That's ok for stuff already _in_ MS product; what about the question of > importing data from external systems that may have different encoding between > them? Looks like that's broken to me... Hmm.., I guess you're talking about dbase date formats and AFAIK these also use DateSerial; - but then that only applies to MS-based or Windows-based dbases. I haven't run into any issues using generic dbases like SQLite, though, but then never did the XP/Vista+ testing thing with that. I do know the dates are correct in my stuff because I set the format to always use the 3-char month abreviation. (Suggests date values univerally use DateSerials to be cross-OS compatible!) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-07 00:06 -0500 |
| Message-ID | <pcomtj$117f$1@gioia.aioe.org> |
| In reply to | #110623 |
On 5/6/2018 8:03 PM, GS wrote: >> On 5/6/2018 7:37 PM, GS wrote: >> ... >> >>> So docs done on pre-Vista systems would still show correct dates when >>> opened in the later OSs because regardless of how the system format >>> displays, the DateSerial dictates the actual date value! >> >> That's ok for stuff already _in_ MS product; what about the question >> of importing data from external systems that may have different >> encoding between them? Looks like that's broken to me... > > Hmm.., I guess you're talking about dbase date formats and AFAIK these > also use DateSerial; - but then that only applies to MS-based or > Windows-based dbases. I haven't run into any issues using generic dbases > like SQLite, though, but then never did the XP/Vista+ testing thing with > that. I do know the dates are correct in my stuff because I set the > format to always use the 3-char month abreviation. (Suggests date values > univerally use DateSerials to be cross-OS compatible!) No, the issue of text from two disparate systems; one encoded as mm/dd/yyyy and the other dd/mm/yyyy. AFAICT, there's no way other than to have to recode to match the system default to get the one not matching to be interpreted correctly. That's just rude... --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-07 03:14 -0400 |
| Message-ID | <pcoucv$m0c$1@dont-email.me> |
| In reply to | #110624 |
> On 5/6/2018 8:03 PM, GS wrote: >>> On 5/6/2018 7:37 PM, GS wrote: >>> ... >>> >>>> So docs done on pre-Vista systems would still show correct dates when >>>> opened in the later OSs because regardless of how the system format >>>> displays, the DateSerial dictates the actual date value! >>> >>> That's ok for stuff already _in_ MS product; what about the question of >>> importing data from external systems that may have different encoding >>> between them? Looks like that's broken to me... >> >> Hmm.., I guess you're talking about dbase date formats and AFAIK these also >> use DateSerial; - but then that only applies to MS-based or Windows-based >> dbases. I haven't run into any issues using generic dbases like SQLite, >> though, but then never did the XP/Vista+ testing thing with that. I do know >> the dates are correct in my stuff because I set the format to always use >> the 3-char month abreviation. (Suggests date values univerally use >> DateSerials to be cross-OS compatible!) > > No, the issue of text from two disparate systems; one encoded as mm/dd/yyyy > and the other dd/mm/yyyy. AFAICT, there's no way other than to have to > recode to match the system default to get the one not matching to be > interpreted correctly. That's just rude... Actually, your example isn't textual dates; - they're numeric! This is where the problem lies. A2 in my exercise is an example of textual date format; - it doesn't matter what the system format is because that format will ALWAYS display correctly. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-07 07:45 -0500 |
| Message-ID | <pcphq4$f08$1@gioia.aioe.org> |
| In reply to | #110625 |
On 5/7/2018 2:14 AM, GS wrote: > Actually, your example isn't textual dates; - they're numeric! This is > where the problem lies. A2 in my exercise is an example of textual date > format; - it doesn't matter what the system format is because that > format will ALWAYS display correctly. It seems like the text ' confused Excel and my little-used Excel "skills" led me down the primrose path...I forgot that 5/5 isn't interpreted as division if don't use the preceding '=' so was trying too hard to force interpretation as date. Using just 5/6 or whatever does get interpreted correctly and one can use a custom format of d/m/yy _or_ m/d/yy OK and mix them; all is well after all...sorry for my miscue on the data entry. The point still is, though, that your example all starts with the date form being known a priori and all the example does is use a non-ambiguous visual format to display the content. There still is no way to determine which of two ambiguous date forms from another system _AS THE TEXT DATE STRING_ is which from the string format alone; and it still isn't totally clear for OP's problem after the explanation whether he has the required information at the point he's trying to solve the problem or not. What I've discovered is that you can still manually force the cell format to interpret the external input correctly by applying the appropriate format but the initial input will be interpreted based on the system setting. I'm used to being able to use MATLAB input forms in which I can specifically define that the input format is what I want irrespective of the system settings. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-07 13:06 -0400 |
| Message-ID | <pcq122$8dc$1@dont-email.me> |
| In reply to | #110628 |
> On 5/7/2018 2:14 AM, GS wrote:
>> Actually, your example isn't textual dates; - they're numeric! This is
>> where the problem lies. A2 in my exercise is an example of textual date
>> format; - it doesn't matter what the system format is because that format
>> will ALWAYS display correctly.
>
> It seems like the text ' confused Excel and my little-used Excel "skills" led
> me down the primrose path...I forgot that 5/5 isn't interpreted as division
> if don't use the preceding '=' so was trying too hard to force interpretation
> as date.
>
> Using just 5/6 or whatever does get interpreted correctly and one can use a
> custom format of d/m/yy _or_ m/d/yy OK and mix them; all is well after
> all...sorry for my miscue on the data entry.
>
> The point still is, though, that your example all starts with the date form
> being known a priori and all the example does is use a non-ambiguous visual
> format to display the content.
>
> There still is no way to determine which of two ambiguous date forms from
> another system _AS THE TEXT DATE STRING_ is which from the string format
> alone; and it still isn't totally clear for OP's problem after the
> explanation whether he has the required information at the point he's trying
> to solve the problem or not.
>
> What I've discovered is that you can still manually force the cell format to
> interpret the external input correctly by applying the appropriate format but
> the initial input will be interpreted based on the system setting. I'm used
> to being able to use MATLAB input forms in which I can specifically define
> that the input format is what I want irrespective of the system settings.
If the source data is indeed StringText then you're at the mercy of Excel
interpreting as per system date format and the resulting ambiguity. Using
textual date formats ("May 05, 2018") will ALWAYS display correctly because
Excel will indeed treat them as text. (ergo not useable in formulas by direct
ref to the cell)
If the source data uses date formats then what displays is a DateSerial in the
chosen format. In this case Excel will use the DateSerial and render it in the
format of its target cell. All is well!
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-07 13:53 -0500 |
| Message-ID | <pcq7c2$1m2m$1@gioia.aioe.org> |
| In reply to | #110630 |
On 5/7/2018 12:06 PM, GS wrote:
...
> If the source data is indeed StringText then you're at the mercy of
> Excel interpreting as per system date format and the resulting
> ambiguity. Using textual date formats ("May 05, 2018") will ALWAYS
> display correctly because Excel will indeed treat them as text. (ergo
> not useable in formulas by direct ref to the cell)
...
Yes, but sometimes with foreign systems one doesn't have the ability to
change the output form...sad, but ime, more often than one would think,
still all too true for specialty systems from a hardware vendor or the
like that are just simple prepackaged demos of how to use the system but
also ime, probably 70-80% of the clients would never have written a
better tool for their purpose but instead just make do with the toy
sample from the vendor and live with the warts.
Been burnt too many times.... :(
--
[toc] | [prev] | [next] | [standalone]
| From | malone <malone@nospam.net.nz> |
|---|---|
| Date | 2018-05-08 08:19 +1200 |
| Message-ID | <pcqccs$1ho$1@dont-email.me> |
| In reply to | #110634 |
On 8-May-2018 6:53 am, dpb wrote:
> On 5/7/2018 12:06 PM, GS wrote:
> ...
>
>> If the source data is indeed StringText then you're at the mercy of
>> Excel interpreting as per system date format and the resulting
>> ambiguity. Using textual date formats ("May 05, 2018") will ALWAYS
>> display correctly because Excel will indeed treat them as text. (ergo
>> not useable in formulas by direct ref to the cell)
> ...
>
> Yes, but sometimes with foreign systems one doesn't have the ability
> to change the output form...sad, but ime, more often than one would
> think, still all too true for specialty systems from a hardware vendor
> or the like that are just simple prepackaged demos of how to use the
> system but also ime, probably 70-80% of the clients would never have
> written a better tool for their purpose but instead just make do with
> the toy sample from the vendor and live with the warts.
>
> Been burnt too many times.... :(
>
> --
>
slightly off topic..
I've been following this thread and have learned things that will help
when I encounter date problems in excel and VBA which I often do.
But what amazes me is that we persist in using these two date formats -
d/m/y and m/d/y. It seems absurd we accept conventions that are
blatantly so ambiguous! And, especially, their continuing use by
software engineers who are expected to think logically. I'm always
coming across applications or data where I have to struggle to work out
which is being used.
d/m/y and m/d/y are not formally internationally accepted date formats
and I'm surprised that there hasn't been a trend towards a more rational
format, such as yyyy-mmm-dd. The very least is a format where the
elements have increasing or decreasing significance - unlike m/d/y which
is neither.
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-07 15:35 -0500 |
| Message-ID | <pcqdb6$ud$1@gioia.aioe.org> |
| In reply to | #110635 |
On 5/7/2018 3:19 PM, malone wrote: ... > But what amazes me is that we persist in using these two date formats - > d/m/y and m/d/y. It seems absurd we accept conventions that are > blatantly so ambiguous! And, especially, their continuing use by > software engineers who are expected to think logically. I'm always > coming across applications or data where I have to struggle to work out > which is being used. > > d/m/y and m/d/y are not formally internationally accepted date formats > and I'm surprised that there hasn't been a trend towards a more rational > format, such as yyyy-mmm-dd. The very least is a format where the > elements have increasing or decreasing significance - unlike m/d/y which > is neither. No, but we 'Murricuns aren't much given to having others tell us what to do; note the similar reluctance for widespread adoption of metric for common use; after nearly 40 yr since was a widespread attempt to enforce, it's still gotten essentially nowhere outside industrial and scientific circles and I suspect another 40 will be about the same. Date convention in US is a similarly ingrown habit; that there are coding standards is pretty-much immaterial to the casual user and I'd venture 70-80% of the spreadsheets in use are done by just that type of individual; may be pretty adept at the usage of Excel interface and all, but little or no actual coding training so they continue to just use everyday convention. As noted in an earlier response, in a 40-yr+/- consulting career in mostly data acq and instrumentation for power utilities I saw a hundred or more data acq systems put together from probably over half that many vendors and every conceivable form and some that were truly incredible for encoding date and time was in the sample set I think. As noted, even vendors would (and I'm sure still do) provide sample code most probably put together by the summer intern that would think something as sophisticated of deliberately using the sequence order of d/m/y as almost revolutionary and, probably, "European". Teaching those folks about serial numbers is a struggle that one rarely wins. As noted, Matlab has a class |datetime| and has enhanced the C i/o format string to read date strings such that one can on a case-by-case basis handle it on input. MS tends to also be heavy-handed in they code as if everything is MS-consistent and leave it up to the user to conform or write the glue to interact. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-07 17:38 -0400 |
| Message-ID | <pcqh1a$3lj$1@dont-email.me> |
| In reply to | #110635 |
> On 8-May-2018 6:53 am, dpb wrote:
>> On 5/7/2018 12:06 PM, GS wrote:
>> ...
>>
>>> If the source data is indeed StringText then you're at the mercy of Excel
>>> interpreting as per system date format and the resulting ambiguity. Using
>>> textual date formats ("May 05, 2018") will ALWAYS display correctly
>>> because Excel will indeed treat them as text. (ergo not useable in
>>> formulas by direct ref to the cell)
>> ...
>>
>> Yes, but sometimes with foreign systems one doesn't have the ability to
>> change the output form...sad, but ime, more often than one would think,
>> still all too true for specialty systems from a hardware vendor or the like
>> that are just simple prepackaged demos of how to use the system but also
>> ime, probably 70-80% of the clients would never have written a better tool
>> for their purpose but instead just make do with the toy sample from the
>> vendor and live with the warts.
>>
>> Been burnt too many times.... :(
>>
>> --
>>
>
> slightly off topic..
>
> I've been following this thread and have learned things that will help when I
> encounter date problems in excel and VBA which I often do.
>
> But what amazes me is that we persist in using these two date formats - d/m/y
> and m/d/y. It seems absurd we accept conventions that are blatantly so
> ambiguous! And, especially, their continuing use by software engineers who
> are expected to think logically. I'm always coming across applications or
> data where I have to struggle to work out which is being used.
>
> d/m/y and m/d/y are not formally internationally accepted date formats and
> I'm surprised that there hasn't been a trend towards a more rational format,
> such as yyyy-mmm-dd. The very least is a format where the elements have
> increasing or decreasing significance - unlike m/d/y which is neither.
Well stated!
My preference is yyyy-mmm-dd (1st) or mmm-dd-yyyy (2nd) for legal purposes,
otherwise mmm-dd or dd-mmm for storing date values. In all cases the 3 char
month abbreviation is used. Using yyyy in the 1st element position (yyyy-mm-dd)
just makes sense for sorting purposes, IMO:)
Thankfully, most development languages use DateSerial and offer text formatting
options as to how the developer wants that to be displayed. But as you state, I
can't understand why they persist to use an ambiguous format; - after all, it's
not like they don't know how systems they develop for work!
--
Garry
Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-07 17:26 -0500 |
| Message-ID | <pcqjqh$av5$1@gioia.aioe.org> |
| In reply to | #110637 |
On 5/7/2018 4:38 PM, GS wrote: > "...most development languages use DateSerial" Well, let's say most have a library available for the developer _to_ use. :) MS, of course, has a problem in that they think 1900 was a leap year so are off by one compared to those who don't (correctly). I don't know which way the C library work or POSIX or whether there's an interpretation on how should work. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-08 00:19 -0400 |
| Message-ID | <pcr8he$648$1@dont-email.me> |
| In reply to | #110639 |
> On 5/7/2018 4:38 PM, GS wrote: >> "...most development languages use DateSerial" > > Well, let's say most have a library available for the developer _to_ use. :) Actually, for VB[A] it's called a runtime.dll which is, as noted, the dev app's library. The developer has no control over the functions or methods the lib exposes, though. > > MS, of course, has a problem in that they think 1900 was a leap year so are > off by one compared to those who don't (correctly). I don't know which way > the C library work or POSIX or whether there's an interpretation on how > should work. Most langs use DateSerial for date values since this has more-or-less been adopted as a 'global' standard so 'regions' will capture the real value in local formats. I'm thinking, though, that terminology and familiarity with processes has played a role in any misunderstandings that prevailed here. Entering 05/07/2018 into a cell in Excel (or any other spreadsheet) is raw data not text. Entering that same data into a grid control is both raw data and text. Don't know how your MatLab takes values but your dialog here suggests it accepts string values that it uses to process whatever, OR interprets/treats entered values as string data. Does this mean what gets entered is wrapped in ""? (You could enlighten me further on this, if you would please.) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-08 01:31 -0500 |
| Message-ID | <pcrg7h$1ds3$1@gioia.aioe.org> |
| In reply to | #110642 |
On 5/7/2018 11:19 PM, GS wrote:
>> On 5/7/2018 4:38 PM, GS wrote:
>>> "...most development languages use DateSerial"
>>
>> Well, let's say most have a library available for the developer _to_
>> use. :)
>
> Actually, for VB[A] it's called a runtime.dll which is, as noted, the
> dev app's library. The developer has no control over the functions or
> methods the lib exposes, though.
For the most part it's just a wrapper around the C RTL and you can call
C RTL directly if wish (or roll your own)...
>> MS, of course, has a problem in that they think 1900 was a leap year
>> so are off by one compared to those who don't (correctly). I don't
>> know which way the C library work or POSIX or whether there's an
>> interpretation on how should work.
>
> Most langs use DateSerial for date values since this has more-or-less
> been adopted as a 'global' standard so 'regions' will capture the real
> value in local formats.
I still say "the language" does no such thing; the language may or may
not include the library but it's entirely up to the developer to use
whatever package or encoding they choose.
> I'm thinking, though, that terminology and familiarity with processes
> has played a role in any misunderstandings that prevailed here. Entering
> 05/07/2018 into a cell in Excel (or any other spreadsheet) is raw data
> not text. Entering that same data into a grid control is both raw data
> and text.
It's ASCII characters typed or pasted in from clipboard or whatever
other source, that's "text". How Excel interprets it is parsing that
text; spreadsheets have their rules which are quite complex as compared
to procedural languages; that's their advantage as being a much
"friendlier" coding paradigm for users that are not traditional programmers.
> Don't know how your MatLab takes values but your dialog here suggests it
> accepts string values that it uses to process whatever, OR
> interprets/treats entered values as string data. Does this mean what
> gets entered is wrapped in ""? (You could enlighten me further on this,
> if you would please.)
Depends on the source, and how one is using it...at the command line
it's much like working at the Immediate window in Excel VBA; one simply
writes executable statements in Matlab syntax (which is very much
Fortran/VB-like in form) and strings those together to achieve a goal.
Of course, for applications or repeatable tasks, one writes code
("m-files") for functions (local scope) and/or scripts (global) and
strings them together just like writing a VB app excepting one doesn't
have the container of the spreadsheet; one handles all data as either
native arrays or higher-level constructs of structs, tables, etc., etc.,
... ML is different from VB in that it is weakly typed in that there
are no variable declaration statements; the double is the default type,
everything else is a cast() to some other type.
As far as strings, the original base language treated them like F77
Fortran--as arrays of char(). Cell arrays were introduced which operate
much like a Variant in VB in holding mixed types and with it special
syntax for the cellstr() string; fairly recently a new strings() class
has been introduced which looks and acts much more like a VB string.
As far as quoted strings; they're recognized by the '%q' C-like format
string in context of fscanf() and friends or in other higher-level
routines that are part of the base package of Matlab such as readtable()
or textscan() or csvread(). The base Matlab package consists of
hundreds of these functions, the most fundamental of which are compiled
code, the high-level ones are often m-files themselves and distributed
with the product as source and are interpreted no differently than
user-written functions.
--
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-08 07:45 -0400 |
| Message-ID | <pcs2kp$s2j$1@dont-email.me> |
| In reply to | #110644 |
Thanks for the details, ..very interesting stuff! > I still say "the language" does no such thing; the language may or may not > include the library but it's entirely up to the developer to use whatever > package or encoding they choose. Well I'm going to flatly assert that you're wrong based on the 5/6 languages I've studied/used because all of them returned a DateSerial property for values stored in Date data Types. (I'm not just a VB[A] guy)<g> How Excel interprets date values has nothing to do with VB[A]; - it just happens to also be a function of how spreadsheets work. (I'm also not just an Excel guy)<g> -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-08 09:45 -0500 |
| Message-ID | <pcsd6c$12hj$1@gioia.aioe.org> |
| In reply to | #110645 |
On 5/8/2018 6:45 AM, GS wrote:
> Thanks for the details, ..very interesting stuff!
>
>> I still say "the language" does no such thing; the language may or may
>> not include the library but it's entirely up to the developer to use
>> whatever package or encoding they choose.
>
> Well I'm going to flatly assert that you're wrong based on the 5/6
> languages I've studied/used because all of them returned a DateSerial
> property for values stored in Date data Types. (I'm not just a VB[A]
> guy)<g>
Of course the native or companion library will return its type; it can
hardly do anything else. :) But...that's not of which I speak here.
You're conflating different things...that there _may_ be an intrinsic
Date data type has no bearing upon whether the developer does/does not
use it for any give application or feature within an application built
in the language. Of course, if the language either natively or with a
companion library supports such a thing, it only makes sense to use it.
Returning to the subject here that the OP has time stamps as text
strings from a data acq app, it's quite possible all it does is read a
real-time clock and never does anything except record that as a string
and so never has a time-type variable of whatever ilk the development
language may support.
Fortran doesn't have a native date type at all; in Matlab, the original
datenum is just a double and while is a serial number doesn't use the
same epoch as does Excel (there are supplied translation routines).
With the introduction of OOP features into Matlab, Mathworks has begun
implementing new classes, the datetime() is one which is an enhancement
of datenum implemented as an opaque object with methods and properties
that provide similar functionality as you're used to with Excel
operations on its Date. There's another whole thing called a
timetable() that is a rectangular collection of things associated with a
specific time sequence that is intended for such things as what it
appears the OP has here.
> How Excel interprets date values has nothing to do with VB[A]; - it just
> happens to also be a function of how spreadsheets work. (I'm also not
> just an Excel guy)<g>
Again, POV...spreadsheets work how they do because that's the way the
developers used the underlying programming language to make them work!
That they used the base features of that language as model or directly
is hardly surprising and then build upon those to provide for the
specific desired functionality. How MS chose to interpret input dates
in Excel has to do with their penchant for thinking the world should
rotate around MS as much as anything else; that the system has no way
other than global change to the locale setting for importing dates from
alternate sources is symptomatic.
OTOH, Matlab has the facility to read from whatever input source in a
user-defined format for any particular variable including conflicting
definitions for two adjacent columns in the same data file if that were
to be the way the data had been produced. AFAICT, that's not possible
in Excel without either first preprocessing the data to produce either a
system-locale consistent format, an unambiguous format recognizable by
Excel or to force to not be interpreted as dates at all and do the
conversion internally. In my view, "that's just rude!" and negates much
of the point of having a general programming language if one can't truly
be general; it's the computer that should bow to the user, not the other
way 'round.
$0.02, imo, ymmv, etc., etc., etc., ... :)
To illustrate, an example of what the OP might be encountering as I
understand it. The following is cut 'n paste from the Matlab command
window to answer more about what interactive input "looks like". No,
one doesn't have to quote strings as input nor is it a string-processing
language like REXX (altho to input to a string variable that text would
have to be quoted, of course, to prevent the interpreter from trying to
execute whatever the text was as code).
>> type dates.txt % a short data file contains two ambiguous dates
5/6/2018, 5/6/2018
1) Read as m/d/yyyy for both...
>>
readtable('dates.txt','readvariablenames',0,'format',repmat('%{M/d/yyyy}D',1,2))
ans =
1×2 table
Var1 Var2
________ ________
5/6/2018 5/6/2018
>> month(ans{1,:}) % see what we got...
ans =
5 5
2) now presume one is from that other data acq system we can't change...
>>
readtable('dates.txt','readvariablenames',0,'format',['%{M/d/yyyy}D','%{d/M/yyyy}D'])
ans =
1×2 table
Var1 Var2
________ ________
5/6/2018 5/6/2018
>> month(ans{1,:}) % see what we got...
ans =
5 6
>>
Indeed, we got the two imported correctly even though they have
different encodings and are visually ambiguous--much better scenario
than having to try to guess and figure out after the fact what Excel did
behind our back.
By default, Matlab keeps the input format for display format so both
still visually look the same; we can fix that, too--
>>
d2=readtable('dates.txt','readvariablenames',0,'format',['%{M/d/yyyy}D','%{d/M/yyyy}D']);
>> d=d2.Var2; % retrieve to work on one variable
>> d.Format='M/d/yyyy'; % make formatting consistent for all
>> d2.Var2=d % put back into the table...
d2 =
1×2 table
Var1 Var2
________ ________
5/6/2018 6/5/2018
>>
One can use whatever display format wanted, of course, so can also use
>> d.Format='dMMMyyyy'
d =
datetime
5Jun2018
>>
And, of course, there also operations on them as one would expect--
>> d2.Var2-d2.Var1
ans =
duration
720:00:00
>> days(ans)
ans =
30
>>
The duration type also has format property for display, default is hours
and there are operations for fixed 24-hr days or calendar durations
depending on whether it's pure time or calendar time one is interested
in, etc., etc., etc., ...
Just scratching the surface; if you were interested, there's Octave
which is a Matlab workalike--it has _most_ of what Matlab has in base
product with some other features that aren't (and vice versa). I've not
checked but I presume by now it has similarly functional datetime class.
--
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-08 14:34 -0400 |
| Message-ID | <pcsqk7$8rj$1@dont-email.me> |
| In reply to | #110646 |
Ok, how MatLab handles this isn't relevant; how Excel handles it is all that matters here! If the target cells are pre-formatted (ergo using a template) to receive their expected data then both columns in your example will display correctly when importing the text. AFAIC, anyone arbitrarily 'dumping' data into a default blank worksheet that's not pre-designed to receive it needs schooling on how to use Excel! (But then, what do I know?) Since most other spreadsheets (at least those that claim to read/write Excel formats) are compatible by attrition of competiveness with Excel, also handle NumberFormats same as Excel does. Numeric date formats are no longer viable because of OS/Local system formats differing as per. People need to stop using them and use textual format where the month is at least "mmm", regardless of which element it occupies. The various spreadsheets will interpret correctly based on DateSerial. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-08 14:09 -0500 |
| Message-ID | <pcssl6$1v6q$1@gioia.aioe.org> |
| In reply to | #110647 |
On 5/8/2018 1:34 PM, GS wrote: > Ok, how MatLab handles this isn't relevant; how Excel handles it is all > that matters here! Trudat; I just included it as thought you found the comparisons of some personal interest if nothing else...it is how I avoid the grief personally by essentially not using spreadsheets. :) > If the target cells are pre-formatted (ergo using a template) to receive > their expected data then both columns in your example will display > correctly when importing the text. AFAIC, anyone arbitrarily 'dumping' > data into a default blank worksheet that's not pre-designed to receive > it needs schooling on how to use Excel! (But then, what do I know?) Ah! OK, that would answer the followup I posed (I think)...but in my experience, the number of people tasked with performing analyses that have ever had any specific Excel training that would lend them to understanding this point is miniscule at best and, at least ime at the utilities and other places I've consulted could best be approximated as "none". I must admit I'm not certain how one goes about doing that; I tried to "preformat" the cells and Excel wouldn't let me do anything to them until there was something else in the cell already...this is past my level of competence with Excel; "dumping data into a blank sheet" is all I've ever known to do myself! :) > Since most other spreadsheets (at least those that claim to read/write > Excel formats) are compatible by attrition of competiveness with Excel, > also handle NumberFormats same as Excel does. > > Numeric date formats are no longer viable because of OS/Local system > formats differing as per. People need to stop using them and use textual > format where the month is at least "mmm", regardless of which element it > occupies. The various spreadsheets will interpret correctly based on > DateSerial. That's a noble objective; in the real world I've lived in it just "ain't agonna' happen!" and, much like the Fortran world from whence my background arises, there's just too much legacy code and applications out there that replacing them all is cost prohibitive and also not happening. Anyways, if/when you see and care to address the recent follow up I think we have clearly reached the end of the fruitfulness of the discussion...altho I, too, have learned something (and may learn something more) re: "how Excel works" that will have to keep in mind going forward. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-08 17:06 -0400 |
| Message-ID | <pct3h5$a8l$1@dont-email.me> |
| In reply to | #110650 |
> Trudat; I just included it as thought you found the comparisons of some > personal interest if nothing else...it is how I avoid the grief personally by > essentially not using spreadsheets Yes indeed! Thanks again for the details; - saves me reading the MatLab userguide! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-08 13:48 -0500 |
| Message-ID | <pcsreb$1sv9$1@gioia.aioe.org> |
| In reply to | #110646 |
On 5/8/2018 9:45 AM, dpb wrote: ... > ... How MS chose to interpret input dates > in Excel has to do with their penchant for thinking the world should > rotate around MS as much as anything else; that the system has no way > other than global change to the locale setting for importing dates from > alternate sources is symptomatic. Back to the Excel-specific issue; my test that to me looks like it shows that Excel is broke was to 1) Enter 5/6/2017 into A1 and A2 initially formatted as General 2) Observe the Excel converted both to Date as mm/dd/yyyy 3) Apply Custom format of dd/mm/yyyy to A2 4) Observe the display change to 6/5/2018 -- looks good, doesn't it? 5) Enter =A1 and =A2 into B1 and B2 6) Format B1 and B2 as d-mmm 7) Observe both read 6-May!!! If one places focus on B2, one observes that it still contains 5/6/2018 and so all Excel has done is to change the output format; it doesn't "believe" you really mean the data itself is in that format and fix it; it's already past that stage and internally created the serial number for the date as per the system locale setting. That's why I have concern that the OP has a real problem in that what he can display as _appearing_ correct for the two sets of data from the foreign system very well may not actually be right at all. Am I seeing this wrong, somehow? I'm certain that if one knows which cell contains data from which source one can write code to fix it, but afaict there's no way to get both into the spreadsheet correctly without some such machination. This also makes me wonder what happens to existing data if the locale is change altho I've not tried to see... It just looks to me like MS has used too big a club and made too many assumptions regarding input format. Now again, yes, using a non-ambiguous format for the external system would solve the issue; but that's not what OP has...whether he can fix the system itself is unknown; as bad as that situation is, I certainly have seen very many which don't provide that flexibility to the end user. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-08 15:02 -0400 |
| Message-ID | <pcss7k$ko7$1@dont-email.me> |
| In reply to | #110648 |
> 1) Enter 5/6/2017 into A1 and A2 initially formatted as General > 2) Observe the Excel converted both to Date as mm/dd/yyyy > 3) Apply Custom format of dd/mm/yyyy to A2 > 4) Observe the display change to 6/5/2018 -- looks good, doesn't it? > 5) Enter =A1 and =A2 into B1 and B2 > 6) Format B1 and B2 as d-mmm > 7) Observe both read 6-May!!! Of course it does! What were you expecting? If you format all the cells as Text, you'll see that the underlying DateSerial for the value entered is 43226 (May 6, 2018). It doesn't matter how you format it to display because the DateSerial drives the result. My point is *why deliberately use an ambiguous format* to display dates given the issue? This is what my exercise demos! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion
[toc] | [prev] | [next] | [standalone]
Page 2 of 3 — ← Prev page 1 [2] 3 Next page →
Back to top | Article view | microsoft.public.excel.programming
csiph-web