Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #41   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Check how date is entered

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
  #42   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 3
Default Check how date is entered

El viernes, 4 de mayo de 2018, 2:31:09 (UTC-5), escribió:
Is there anyway to check with VBA if a date is entered as DD-MM-YY or MM-DD-YY?

How can I tell using VBA if 4-5-18 is actually 4th of May of Fifth of April?

Jan



x="4-5-18"
?format(x,"mm/dd/yyyy") 'Return 05/04/2018
?format(x,"Long date") 'Return viernes, 4 de mayo de 2018

  #43   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Check how date is entered

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.

--



  #44   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Check how date is entered

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
  #45   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Check how date is entered

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,'forma t',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,'forma t',['%{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,'fo rmat',['%{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.

--


  #46   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Check how date is entered

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
  #47   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Check how date is entered

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.

--
  #48   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Check how date is entered

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
  #49   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Check how date is entered

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) "how Excel works" that will have to keep in mind
going forward.

--
  #50   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Check how date is entered

On 5/8/2018 2:02 PM, GS wrote:
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?


Well, I wasn't absolutely positive which is why I did it! :)

My previous thought was that the date format selection actually did do
an implicit conversion rather than being just the format of moving
fields around. That there's reasons I grok; I was "underneath the
impression" as my former colleague Mr. Malaprop was wont to say that one
could manage to recast that way; clearly I was wrong and I'm guessing in
the subject thread the OP is as well.

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


Well, as the other follow-up comments, sometimes one doesn't have the
choice of what input format to use and I was trying to figure out how
the OP could solve his problem under those constraints.

Sometimes ideal solutions are just not available in the real world.

Can you illustrate the idea of the template for the ignorant? :)

--






  #51   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Check how date is entered


Well, as the other follow-up comments, sometimes one doesn't have the choice
of what input format to use and I was trying to figure out how the OP could
solve his problem under those constraints.

Sometimes ideal solutions are just not available in the real world.

Can you illustrate the idea of the template for the ignorant? :)


Sure! (Though I don't consider you to be ignorant! You seem to be quite
knowledgeable despite some unfamiliarity working in Excel after being so
entrenched in MatLab)

My solutions, as you know from our collaboration project, are primarily *task
oriented* to service repetative processing of data on a regular or period-based
schedule. In cases where raw data is imported to a spreadsheet I'll pre-design
a template to receive that data in its _expected_ format so it displays in the
user's _desired_ format. After all, output data from most apps is usually just
plain text. (CSV/TSV or the like)

Designing worksheets to receive raw or formatted data correctly is a critical
part of my job as an Excel developer. Automating the solution is also another
critical part of the job so it enhances user productivity in a consistent
reliable manner.

Sometimes the data is stored in Excel files which may or may not have
formatting, and sometimes the data is stored in a dbase (also with data type
formatting). In both these cases I'd use ADODB to load the data into
recordsets, then dump it into the worksheet which is already preformatted to
receive it. (ergo a pre-designed 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
  #52   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Check how date is entered

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
  #53   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Check how date is entered

On 5/8/2018 4:04 PM, GS wrote:
....

Sure! (Though I don't consider you to be ignorant! ...


Blushes... :) You're too kind!

--

  #54   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Check how date is entered

On 5/8/2018 4:04 PM, GS wrote:
....

... In cases where raw data is imported to a
spreadsheet I'll pre-design a template to receive that data in its
_expected_ format so it displays in the user's _desired_ format. After
all, output data from most apps is usually just plain text. (CSV/TSV or
the like)

....

Well, I tried the following:

1) created a sheet like the previous example with the two formats
m/d/yyyy, d/m/yyyy in A1, B1, respectively containing dummy data.

2) saved the file

3) wrote to those two cells via the ML COM routine the content of the
same as previous text file...

dates=importdata('dates.txt'); % read the file to a cell string
dates=strip(split(dates)); % split at the ',' and clean
xlswrite('dates.xls',dates,1,'A1:B1') % and put into the formatted cells

Unfortunately, that doesn't seem to override the MS penchant for
interpreting by system locale; the format is OK, but the serial date is
still based on m/d/yyyy.

I don't know much about Excel, but that's about the only way
programmatically to push data into it I know; I was able to manually
import the file as text with the import tool but it allows you to set
the date format on a column-by-column basis; I suppose maybe there's
some way to emulate this programmatically?

All in all, Excel continues to make me exceedingly glad I don't have to
try to use it for engineering work... :)

And, yes, I understand this is trying to fix a sorry way to run a
railroad...but it seems to be the OPs RR. :)

--
  #55   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Check how date is entered

On 5/8/2018 4:04 PM, GS wrote:
...

... In cases where raw data is imported to a spreadsheet I'll pre-design a
template to receive that data in its _expected_ format so it displays in
the user's _desired_ format. After all, output data from most apps is
usually just plain text. (CSV/TSV or the like)

...

Well, I tried the following:

1) created a sheet like the previous example with the two formats
m/d/yyyy, d/m/yyyy in A1, B1, respectively containing dummy data.

2) saved the file

3) wrote to those two cells via the ML COM routine the content of the
same as previous text file...

dates=importdata('dates.txt'); % read the file to a cell string
dates=strip(split(dates)); % split at the ',' and clean
xlswrite('dates.xls',dates,1,'A1:B1') % and put into the formatted cells

Unfortunately, that doesn't seem to override the MS penchant for interpreting
by system locale; the format is OK, but the serial date is still based on
m/d/yyyy.

I don't know much about Excel, but that's about the only way programmatically
to push data into it I know; I was able to manually import the file as text
with the import tool but it allows you to set the date format on a
column-by-column basis; I suppose maybe there's some way to emulate this
programmatically?


Absolutely! See below...


All in all, Excel continues to make me exceedingly glad I don't have to try
to use it for engineering work... :)

And, yes, I understand this is trying to fix a sorry way to run a
railroad...but it seems to be the OPs RR. :)


Yeah, I don't have much use for Excel's built-in import feature. In solutions
where data gets imported from plain text files, the entire process is managed
by VB[A] because the import utility will nearly almost always misinterpret the
source data. (Or at very least, cannot be trusted to interpret accurately left
to its own!)

Now as for my VB6 apps using the fpSpread.ocx, this spreadsheet control uses
cell types so importing text source data is treated according to its target
cell. How that cell displays its data is determined by its preset format. (The
fpSpread.ocx does not support ConditionalFormatting and so this has to be done
programmatically when cell type formats are not preset. In this control, date
cells will optionally include a popup calendar so numeric input is not part of
the process entering the date value because it knows which day of which month
page you selected.) Likewise, cell formats and/or types can be set
programmatically before the data is loaded into a worksheet.

This is, IMO, a better way to handle dates than Excel offers and so as I'm able
to reproduce here what Excel features are missing, so also can I reproduce in
Excel programmatically what it lacks.

The concept here is this: worksheets can be predesigned to receive data types
with specific formats OR be prepared for data types before loading at runtime.
Predesigning worksheets in a project workbook is how templates are created.
Note, though, that not all sheets in a project workbook need to be predesigned;
- just those that receive imported data.:)

Even when the source data is generated by another app with known date formats,
this can be programmatically managed so the expected format converts to the
desired (target) format correctly. This puts you (or your solution) in control,
not Excel (or whatever target spreadsheet). Unfortunately, not all non-MS
spreadsheets support scripting and so is why I chose a programmable spreadsheet
control to duplicate my Excel-based apps as stand-alone Win apps back when MS
introduced the Ribbon UI in MSO2007. This enables non MSO users to have the
same solutions as MSO users without all the distractions of the Excel UI or its
consequent behaviors. (The Excel versions can totally control the UI but not
Excel's behaviors.)

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion


  #56   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Check how date is entered

On 5/8/2018 10:51 PM, GS wrote:
....

Yeah, I don't have much use for Excel's built-in import feature. In
solutions where data gets imported from plain text files, the entire
process is managed by VB[A] because the import utility will nearly
almost always misinterpret the source data. (Or at very least, cannot be
trusted to interpret accurately left to its own!)

Now as for my VB6 apps using the fpSpread.ocx, this spreadsheet control
uses cell types so importing text source data is treated according to
its target cell. How that cell displays its data is determined by its
preset format. (The fpSpread.ocx does not support ConditionalFormatting
and so this has to be done programmatically when cell type formats are
not preset. In this control, date cells will optionally include a popup
calendar so numeric input is not part of the process entering the date
value because it knows which day of which month page you selected.)
Likewise, cell formats and/or types can be set programmatically before
the data is loaded into a worksheet.

This is, IMO, a better way to handle dates than Excel offers and so as
I'm able to reproduce here what Excel features are missing, so also can
I reproduce in Excel programmatically what it lacks.

....

OK, so essentially you throw the baby out with the bath water... :)

Yeah, w/o additional tools on the Excel end, I'd write "glue" code to do
what I illustrated earlier, read the data with a tool I know I can
control to import the given format correctly before stuffing it into
Excel (your solution a different route in essence) _or_ use an
intermediary filter to do the conversion separately before Excel.

Anyway, that confirms to me Excel is, in effect, broken when you can't
do the above import without resorting to the external tool other than by
the manual, change it every time, route. Something to keep in mind if
ever do get a call again for "real" work other than the (increasingly
rare) call on the plant performance monitors for an update or the like...

--



  #57   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Check how date is entered

On 5/8/2018 10:51 PM, GS wrote:
...

Yeah, I don't have much use for Excel's built-in import feature. In
solutions where data gets imported from plain text files, the entire
process is managed by VB[A] because the import utility will nearly almost
always misinterpret the source data. (Or at very least, cannot be trusted
to interpret accurately left to its own!)

Now as for my VB6 apps using the fpSpread.ocx, this spreadsheet control
uses cell types so importing text source data is treated according to its
target cell. How that cell displays its data is determined by its preset
format. (The fpSpread.ocx does not support ConditionalFormatting and so
this has to be done programmatically when cell type formats are not preset.
In this control, date cells will optionally include a popup calendar so
numeric input is not part of the process entering the date value because it
knows which day of which month page you selected.) Likewise, cell formats
and/or types can be set programmatically before the data is loaded into a
worksheet.

This is, IMO, a better way to handle dates than Excel offers and so as I'm
able to reproduce here what Excel features are missing, so also can I
reproduce in Excel programmatically what it lacks.

...

OK, so essentially you throw the baby out with the bath water... :)

Yeah, w/o additional tools on the Excel end, I'd write "glue" code to do what
I illustrated earlier, read the data with a tool I know I can control to
import the given format correctly before stuffing it into Excel (your
solution a different route in essence) _or_ use an intermediary filter to do
the conversion separately before Excel.

Anyway, that confirms to me Excel is, in effect, broken when you can't do the
above import without resorting to the external tool other than by the manual,
change it every time, route. Something to keep in mind if ever do get a call
again for "real" work other than the (increasingly rare) call on the plant
performance monitors for an update or the like...


Well, my solution is VBA automation of the process, which is not an external
tool. In essence, though, I agree that this part of Excel is indeed not well
thought out for things beyond general data import.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #58   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Check how date is entered

On 5/9/2018 10:32 AM, GS wrote:
....

Well, my solution is VBA automation of the process, which is not an
external tool. In essence, though, I agree that this part of Excel is
indeed not well thought out for things beyond general data import.


Was referring to (what I think is a 3rd party?) tool, fpSpread.ocx
DAGS for the filename and came up dry, though; who publishes it just out
of curiosity?

Could, in all likelihood even write wrapper glue code to drive it from
Matlab if one wanted to badly enough! :)

"Not well thought out" is a polite euphemism for "broke"? :)

--



  #59   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1,182
Default Check how date is entered

On 5/9/2018 10:32 AM, GS wrote:
...

Well, my solution is VBA automation of the process, which is not an
external tool. In essence, though, I agree that this part of Excel is
indeed not well thought out for things beyond general data import.


Was referring to (what I think is a 3rd party?) tool, fpSpread.ocx
DAGS for the filename and came up dry, though; who publishes it just out of
curiosity?


Ok, this is a deprecated ActiveX component I bought after MS 1st introduced the
Ribbon UI. It supports read/write of early Excel files, but for all intents and
purposes it replaces Excel. I pretty much have complete programmatic control of
it.

It used to be considered the premiere alternative to Excel back in its day, but
now it's been trumped by SpreadsheetGear. There is, though, a newer Spread.net
version that is apparently way better than its predecessor...

https://www.grapecity.com/en/spreadnet

Could, in all likelihood even write wrapper glue code to drive it from Matlab
if one wanted to badly enough! :)

"Not well thought out" is a polite euphemism for "broke"? :)


--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
  #60   Report Post  
Posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
Posts: 109
Default Check how date is entered

On 5/9/2018 2:03 PM, GS wrote:
....

Was referring to (what I think is a 3rd party?) tool, fpSpread.ocx
DAGS for the filename and came up dry, though; who publishes it just
out of curiosity?


Ok, this is a deprecated ActiveX component I bought after MS 1st
introduced the Ribbon UI. It supports read/write of early Excel files,
but for all intents and purposes it replaces Excel. I pretty much have
complete programmatic control of it.

....

AH! "I see more clearly now" what you have said in our other conversations.

--

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
Check for duplicate numbers from ones entered and anoter set Goldie Excel Programming 1 May 29th 09 08:47 AM
Dates - Need to display date one month prior to user-entered date brettopp Excel Worksheet Functions 13 December 3rd 07 06:58 PM
Date subtraction -How to not show negative when 2nd date not entered Edward[_2_] New Users to Excel 2 September 27th 07 03:03 PM
Check Mark appears when a letter is entered in a field Gina Excel Programming 2 February 22nd 07 09:23 PM
Macro to check if data has been entered Mike R. Excel Programming 1 December 21st 04 12:14 AM


All times are GMT +1. The time now is 08:10 PM.

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"