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 3 of 3 — ← Prev page 1 2 [3]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-08 14:59 -0500 |
| Message-ID | <pcsvj0$4f6$1@gioia.aioe.org> |
| In reply to | #110649 |
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? :) --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-08 17:04 -0400 |
| Message-ID | <pct3co$9ap$1@dont-email.me> |
| In reply to | #110651 |
> > 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
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-08 18:53 -0500 |
| Message-ID | <pctd99$omm$1@gioia.aioe.org> |
| In reply to | #110652 |
On 5/8/2018 4:04 PM, GS wrote: ... > Sure! (Though I don't consider you to be ignorant! ... Blushes... :) You're too kind! --
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-08 21:12 -0500 |
| Message-ID | <pctlec$1277$1@gioia.aioe.org> |
| In reply to | #110652 |
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. :)
--
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-08 23:51 -0400 |
| Message-ID | <pctr8e$b3c$1@dont-email.me> |
| In reply to | #110655 |
> 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
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-09 07:20 -0500 |
| Message-ID | <pcup3c$p77$1@gioia.aioe.org> |
| In reply to | #110656 |
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... --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-09 11:32 -0400 |
| Message-ID | <pcv49o$a4k$1@dont-email.me> |
| In reply to | #110657 |
> 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
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-09 11:35 -0500 |
| Message-ID | <pcv80f$1lk9$1@gioia.aioe.org> |
| In reply to | #110658 |
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"? :) --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-09 15:03 -0400 |
| Message-ID | <pcvgm7$6hb$1@dont-email.me> |
| In reply to | #110659 |
> 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
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-09 15:18 -0500 |
| Message-ID | <pcvl23$dh7$1@gioia.aioe.org> |
| In reply to | #110660 |
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. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-07 13:34 -0400 |
| Message-ID | <pcq2ne$lha$1@dont-email.me> |
| In reply to | #110628 |
> 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. The OP's Q: "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?" Auric's reply answers this accurately! -- 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:44 -0500 |
| Message-ID | <pcq6r5$1l5i$1@gioia.aioe.org> |
| In reply to | #110632 |
On 5/7/2018 12:34 PM, GS wrote: >> 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. > > The OP's Q: > "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?" > > Auric's reply answers this accurately! That's true and acknowledged. What we don't know is whether the correct answer to the question asked is the answer to the underlying problem or not; insufficient data to tell. What the answer is is what Excel thinks the data are and how will be exported; what we don't know is whether that is the same as what the foreign system thinks they are for the two cases--my gut feeling is "there be dragons!" but don't know for sure. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-07 17:40 -0400 |
| Message-ID | <pcqh4f$42t$1@dont-email.me> |
| In reply to | #110633 |
> On 5/7/2018 12:34 PM, GS wrote: >>> 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. >> >> The OP's Q: >> "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?" >> >> Auric's reply answers this accurately! > > That's true and acknowledged. What we don't know is whether the correct > answer to the question asked is the answer to the underlying problem or not; > insufficient data to tell. > > What the answer is is what Excel thinks the data are and how will be > exported; what we don't know is whether that is the same as what the foreign > system thinks they are for the two cases--my gut feeling is "there be > dragons!" but don't know for sure. Agreed! -- 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:50 -0500 |
| Message-ID | <pcql87$cnd$1@gioia.aioe.org> |
| In reply to | #110638 |
On 5/7/2018 4:40 PM, GS wrote: ... >> What the answer is is what Excel thinks the data are and how will be >> exported; what we don't know is whether that is the same as what the >> foreign system thinks they are for the two cases--my gut feeling is >> "there be dragons!" but don't know for sure. > > Agreed! Ah! We finally(!) got to the point was trying to make... :) There's "knowing" and then there's "knowing"...and the two aren't always necessarily the same. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-08 00:02 -0400 |
| Message-ID | <pcr7hn$1hv$1@dont-email.me> |
| In reply to | #110640 |
> On 5/7/2018 4:40 PM, GS wrote: > ... > >>> What the answer is is what Excel thinks the data are and how will be >>> exported; what we don't know is whether that is the same as what the >>> foreign system thinks they are for the two cases--my gut feeling is "there >>> be dragons!" but don't know for sure. >> >> Agreed! > > Ah! We finally(!) got to the point was trying to make... :) > > There's "knowing" and then there's "knowing"...and the two aren't always > necessarily the same. Very true:) -- 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 | jan120253@gmail.com |
|---|---|
| Date | 2018-05-07 01:45 -0700 |
| Message-ID | <867f59a1-49a2-43ee-9685-73cb416f7c33@googlegroups.com> |
| In reply to | #110602 |
I'm sorry it has taken me so long to get back, but I have been away from my computer ever since. I will try some of you suggestions and see if any of them solves my challenge. Thank you very much for your efforts. Jan
[toc] | [prev] | [next] | [standalone]
| From | jan120253@gmail.com |
|---|---|
| Date | 2018-05-07 01:52 -0700 |
| Message-ID | <53aec81a-6534-471f-938b-e5784a0eb538@googlegroups.com> |
| In reply to | #110626 |
And an explanation for my problem: The original issue was and is, that I programmatically have to change alle dates to dd-mm-yyyy format. The dates are imported from other systems, and some of these are in the format mm-dd-yyyy. Dates that are already dd-mm-yyyy shall not be converted, so I want to check the format before conversion. When the sheet is done, the data will exported to another system, who cannot check if the dates are in the right format, but expect them to be dd-mm-yyyy. Therefore I (or rather those who uses the sheet) have to maked sure all dates are formated correctly be the export. Jan
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-07 08:16 -0500 |
| Message-ID | <pcpjjc$iba$1@gioia.aioe.org> |
| In reply to | #110627 |
On 5/7/2018 3:52 AM, jan120253@gmail.com wrote: > And an explanation for my problem: > > The original issue was and is, that I programmatically have to change alle dates to dd-mm-yyyy format. The dates are imported from other systems, and some of these are in the format mm-dd-yyyy. Dates that are already dd-mm-yyyy shall not be converted, so I want to check the format before conversion. > > When the sheet is done, the data will exported to another system, who cannot check if the dates are in the right format, but expect them to be dd-mm-yyyy. Therefore I (or rather those who uses the sheet) have to maked sure all dates are formated correctly be the export. If the data are correctly formatted based on the input from whence they came, then there's no reason not to just convert all to dd-mm-yyyy; it's a "do-nothing" for those that already are, so there's no penalty. Auric's function will let you do it selectively, but there's probably not enough of a time penalty that it makes any difference whether use it or not. Where you've still got a problem is if the data were imported but not internally coded to match the external format; then you've still got the issue that the values of ambiguous dates can't be distinguished solely by their string value altho as GS points out, inside Excel they will have been read according to the system default format in which case switching the formatting changes the appearance in the cell but not the value which raises the question of whether the dates in the sheet are actually correct or not depending upon the input source and the system setting and in what form the values were on input--was it from a date string or a serial number? --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-07 13:10 -0400 |
| Message-ID | <pcq19s$ag1$1@dont-email.me> |
| In reply to | #110629 |
> "as GS points out, inside Excel they will have been read according to the > system default format" See my latest reply to yours regarding "DateText" vs date interpretation of text being imported with date values. -- 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 | Ramon-México <cesaramon@gmail.com> |
|---|---|
| Date | 2018-05-07 22:03 -0700 |
| Message-ID | <0ef8d337-3bb7-462d-bc32-ebad2615293e@googlegroups.com> |
| In reply to | #110602 |
El viernes, 4 de mayo de 2018, 2:31:09 (UTC-5), jan1...@gmail.com 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
[toc] | [prev] | [standalone]
Page 3 of 3 — ← Prev page 1 2 [3]
Back to top | Article view | microsoft.public.excel.programming
csiph-web