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 1 of 3 [1] 2 3 Next page →
| From | jan120253@gmail.com |
|---|---|
| Date | 2018-05-04 00:31 -0700 |
| Subject | Check how date is entered |
| Message-ID | <073d347c-a7b6-4718-94a4-d9fe2d6d2822@googlegroups.com> |
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
[toc] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-04 07:46 -0500 |
| Message-ID | <pchkn6$djt$1@gioia.aioe.org> |
| In reply to | #110602 |
On 5/4/2018 2:31 AM, jan120253@gmail.com wrote: > 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? If that's all you have in isolation you can't...if there are a string of dates such that can find a value >12 in the (presumed) month field then you can make the presumption that's days and the other must be months but without some additional hints or such a specific day that can be recognized there's just insufficient data to be unequivocal. --
[toc] | [prev] | [next] | [standalone]
| From | "Auric__" <not.my.real@email.address> |
|---|---|
| Date | 2018-05-04 15:55 +0000 |
| Message-ID | <XnsA8D85AD7379auricauricauricauric@85.214.115.223> |
| In reply to | #110603 |
dpb wrote:
> On 5/4/2018 2:31 AM, jan120253@gmail.com wrote:
>> 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?
>
> If that's all you have in isolation you can't...if there are a string of
> dates such that can find a value >12 in the (presumed) month field then
> you can make the presumption that's days and the other must be months
> but without some additional hints or such a specific day that can be
> recognized there's just insufficient data to be unequivocal.
On the other hand, if the cell is properly formatted as a date, you can check
the NumberFormat property:
Dim tmp As Variant
tmp = Split(ActiveCell.NumberFormat, "/")
If UBound(tmp) > 0 Then
Select Case LCase(tmp(0))
Case "d", "dd", "ddd", "dddd"
'd/m/y
Case "m", "mm", "mmm", "mmmm", "mmmmm"
'm/d/y
Case Else
'not formatted as date
End Select
End If
--
- Were you looking for something?
- A sense of the miraculous in everyday life.
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-04 12:43 -0500 |
| Message-ID | <pci64r$1d5a$1@gioia.aioe.org> |
| In reply to | #110604 |
On 5/4/2018 10:55 AM, Auric__ wrote: > dpb wrote: > >> On 5/4/2018 2:31 AM, jan120253@gmail.com wrote: >>> 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? >> >> If that's all you have in isolation you can't...if there are a string of >> dates such that can find a value >12 in the (presumed) month field then >> you can make the presumption that's days and the other must be months >> but without some additional hints or such a specific day that can be >> recognized there's just insufficient data to be unequivocal. > > On the other hand, if the cell is properly formatted as a date, you can check > the NumberFormat property: > > Dim tmp As Variant > tmp = Split(ActiveCell.NumberFormat, "/") > If UBound(tmp) > 0 Then > Select Case LCase(tmp(0)) > Case "d", "dd", "ddd", "dddd" > 'd/m/y > Case "m", "mm", "mmm", "mmmm", "mmmmm" > 'm/d/y > Case Else > 'not formatted as date > End Select > End If > But if the cell is formatted as Date and contains the data, then it will already be interpreted as whichever and all need to do is =MONTH() or =DAY() and inspect return value to know... Didn't seem as that was the OP's question/problem at least way it came across to my reading...guess we'll say if comes back to amplify. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-04 17:37 -0400 |
| Message-ID | <pcijrp$9mi$1@dont-email.me> |
| In reply to | #110605 |
> On 5/4/2018 10:55 AM, Auric__ wrote: >> dpb wrote: >> >>> On 5/4/2018 2:31 AM, jan120253@gmail.com wrote: >>>> 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? >>> >>> If that's all you have in isolation you can't...if there are a string of >>> dates such that can find a value >12 in the (presumed) month field then >>> you can make the presumption that's days and the other must be months >>> but without some additional hints or such a specific day that can be >>> recognized there's just insufficient data to be unequivocal. >> >> On the other hand, if the cell is properly formatted as a date, you can >> check >> the NumberFormat property: >> >> Dim tmp As Variant >> tmp = Split(ActiveCell.NumberFormat, "/") >> If UBound(tmp) > 0 Then >> Select Case LCase(tmp(0)) >> Case "d", "dd", "ddd", "dddd" >> 'd/m/y >> Case "m", "mm", "mmm", "mmmm", "mmmmm" >> 'm/d/y >> Case Else >> 'not formatted as date >> End Select >> End If >> > > But if the cell is formatted as Date and contains the data, then it will > already be interpreted as whichever and all need to do is =MONTH() or =DAY() > and inspect return value to know... > > Didn't seem as that was the OP's question/problem at least way it came across > to my reading...guess we'll say if comes back to amplify. Hmm.., I rather like Auric's simplified solution since it indeed does EXACTLY what I interpret the OP is looking to accomplish. Note also that Excel uses the 'system' date format unless set otherwise for specific cells. For example, after XP the format order for d/m got switched. -- 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-04 18:49 -0500 |
| Message-ID | <pcirib$d6e$1@gioia.aioe.org> |
| In reply to | #110606 |
On 5/4/2018 4:37 PM, GS wrote: ... > Hmm.., I rather like Auric's simplified solution since it indeed does > EXACTLY what I interpret the OP is looking to accomplish. ... Well, it'll tell him what the cell is formatted as; whether that's what the data was when entered isn't determinable from the string which is where _I_ thought OP was coming from... :) Once it's in the cell it can be either depending on the format; is that correct or not is still indeterminate. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-04 21:42 -0400 |
| Message-ID | <pcj274$u2s$1@dont-email.me> |
| In reply to | #110607 |
> On 5/4/2018 4:37 PM, GS wrote:
> ...
>
>> Hmm.., I rather like Auric's simplified solution since it indeed does
>> EXACTLY what I interpret the OP is looking to accomplish.
> ...
>
> Well, it'll tell him what the cell is formatted as; whether that's what the
> data was when entered isn't determinable from the string which is where _I_
> thought OP was coming from... :)
>
> Once it's in the cell it can be either depending on the format; is that
> correct or not is still indeterminate.
Date data will display as various values according to cell.NumberFormat for
Dates. If the cell containing a date is General format then the system date
format will display.
Ambiguities arise when *numeric representation* displays as opposed to *textual
representation* displaying. Auric's code determines whether the 1st element
represents d or m. Isn't this what the OP wants to figure out?
FWIW:
I prefer to use textual representation for dates as...
Where year is assumed to be current, May-4, 4-May
(ie: an accounting ledger/journal for a specific fiscal)
Where year is not assumed: May-4, 2018, May-4-2018, 4-May, 2018, ...
..where the NumberFormat is defined in Custom as "mmm-d" or as respective to
the desired display. This way, it doesn't matter what the system format is and
so there's no ambiguity whatsoever.
--
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-04 23:46 -0500 |
| Message-ID | <pcjd0h$147c$1@gioia.aioe.org> |
| In reply to | #110608 |
On 5/4/2018 8:42 PM, GS wrote: ... > Ambiguities arise when *numeric representation* displays as opposed to > *textual representation* displaying. Auric's code determines whether the > 1st element represents d or m. Isn't this what the OP wants to figure out? ... Yeah, but it depends on what point it is in the process; is it the format of the cell that determines or the data string going into the cell? I was presuming it was the latter in which case how the cell is formatted is the answer to a question as to how Excel interpreted the string but doesn't _necessarily_ mean that was what the date was from whence it came. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-05 04:21 -0400 |
| Message-ID | <pcjpj9$m8e$1@dont-email.me> |
| In reply to | #110609 |
> On 5/4/2018 8:42 PM, GS wrote: > ... > >> Ambiguities arise when *numeric representation* displays as opposed to >> *textual representation* displaying. Auric's code determines whether the >> 1st element represents d or m. Isn't this what the OP wants to figure out? > ... > > Yeah, but it depends on what point it is in the process; is it the format of > the cell that determines or the data string going into the cell? I was > presuming it was the latter in which case how the cell is formatted is the > answer to a question as to how Excel interpreted the string but doesn't > _necessarily_ mean that was what the date was from whence it came. That's not how it works! Dates aren't strings same as currencies arn't strings! Excel uses the *system* date format by default to display the 'DateSerial' in the cell to determine the dmy/mdy of a date based on that system's date format. So.., when working with a file created in XP that has date cells, the DateSerial is used to determine the datevalue in later OSs. This avoids all ambiguity as to the true date being displayed. The ambiguity for the user arises when the cell format is numeric (05/04/2018) if/when the system date format isn't known. Using a textual format eliminates the ambiguity because it displays the month name based on the DateSerial of the value in the date cell. (What's important to remember here is that what cells display isn't necessarily what they contain; -what we see is just how the cells are/were formatted to display their contents. -- 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-05 07:21 -0500 |
| Message-ID | <pck7ln$gck$1@gioia.aioe.org> |
| In reply to | #110610 |
On 5/5/2018 3:21 AM, GS wrote: >> On 5/4/2018 8:42 PM, GS wrote: >> ... >> >>> Ambiguities arise when *numeric representation* displays as opposed >>> to *textual representation* displaying. Auric's code determines >>> whether the 1st element represents d or m. Isn't this what the OP >>> wants to figure out? >> ... >> >> Yeah, but it depends on what point it is in the process; is it the >> format of the cell that determines or the data string going into the >> cell? I was presuming it was the latter in which case how the cell is >> formatted is the answer to a question as to how Excel interpreted the >> string but doesn't _necessarily_ mean that was what the date was from >> whence it came. > > That's not how it works! Dates aren't strings same as currencies arn't > strings! ... I'm fully aware of all that...wasn't what I was addressing which is the date string as the OP wrote it in the query as the only piece of info available, _NOT_ already stored but want to _be_ stored and wondering which way is which to do so. I said specifically the "the data string going _into_ the cell", once it's _in_ the cell, yes, the cell is formatted as one or the other and is unequivocal as far as Excel is concerned. Doesn't _necessarily_ mean it was interpreted correctly when entered, however, dependent upon the source. We're talking past each other about different point in the process; ... I've re-read the OPs ? and _guess_ I now think he probably was meaning retrieved from a cell and therefore already in Excel so knowing which way the cell is formatted answers the question. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-05 09:50 -0400 |
| Message-ID | <pckcqk$pb3$1@dont-email.me> |
| In reply to | #110611 |
No contest on the above... > I've re-read the OPs ? and _guess_ I now think he probably was meaning > retrieved from a cell and therefore already in Excel so knowing which way the > cell is formatted answers the question. Exactly my point; - using a textual format eliminates the ambiguity! Q: How did you get the underlined text? -- 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-05 09:05 -0500 |
| Message-ID | <pckdmt$pqt$1@gioia.aioe.org> |
| In reply to | #110612 |
On 5/5/2018 8:50 AM, GS wrote: > No contest on the above... > > >> I've re-read the OPs ? and _guess_ I now think he probably was meaning >> retrieved from a cell and therefore already in Excel so knowing which >> way the cell is formatted answers the question. > > Exactly my point; - using a textual format eliminates the ambiguity! Well, yes, but OP didn't use one so that's sorta' a moot point...my take when reading the question initially was that he had a date string from somewhere unspecified and was wondering if any way to determine which it might be...if he/she ever comes back and comments maybe we'll find out what the real question was. :) > Q: How did you get the underlined text? Just use underscores; display result 'pends on the newsreader recognizing them; T'Bird does for the most part; not all will/do. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-05 10:33 -0400 |
| Message-ID | <pckfc9$9p3$1@dont-email.me> |
| In reply to | #110613 |
> On 5/5/2018 8:50 AM, GS wrote: >> No contest on the above... >> >> >>> I've re-read the OPs ? and _guess_ I now think he probably was meaning >>> retrieved from a cell and therefore already in Excel so knowing which way >>> the cell is formatted answers the question. >> >> Exactly my point; - using a textual format eliminates the ambiguity! > > Well, yes, but OP didn't use one so that's sorta' a moot point...my take when > reading the question initially was that he had a date string from somewhere > unspecified and was wondering if any way to determine which it might be...if > he/she ever comes back and comments maybe we'll find out what the real > question was. > > :) > >> Q: How did you get the underlined text? > > Just use underscores; display result 'pends on the newsreader recognizing > them; T'Bird does for the most part; not all will/do. I don't see how 2 different characters can occupy the same space and so was expecting the 'flag' char as what we use to boldface, for example. -- 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-05 10:57 -0500 |
| Message-ID | <pckka9$149q$1@gioia.aioe.org> |
| In reply to | #110614 |
On 5/5/2018 9:33 AM, GS wrote: >> On 5/5/2018 8:50 AM, GS wrote: ... >>> Q: How did you get the underlined text? >> >> Just use underscores; display result 'pends on the newsreader >> recognizing them; T'Bird does for the most part; not all will/do. > > I don't see how 2 different characters can occupy the same space and so > was expecting the 'flag' char as what we use to boldface, for example. There aren't two characters; it's unicode "combining characters" character; is dependent upon font in use and the renderer to recognize it; if it works it gives the effect; at worst one gets an idea of the intended effect as just a leading/trailing underscore in plain text on older newsreaders. Not positive which release in T'bird it actually began to look like underlined text; discovered it by pure accident as had used the form for some emphasis on old groups with plain-vanilla readers "like since forever" so is just a result of newer systems with an old and never standard habit. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-05 20:51 -0400 |
| Message-ID | <pcljhr$6nd$1@dont-email.me> |
| In reply to | #110615 |
> On 5/5/2018 9:33 AM, GS wrote: >>> On 5/5/2018 8:50 AM, GS wrote: > ... > >>>> Q: How did you get the underlined text? >>> >>> Just use underscores; display result 'pends on the newsreader recognizing >>> them; T'Bird does for the most part; not all will/do. >> >> I don't see how 2 different characters can occupy the same space and so was >> expecting the 'flag' char as what we use to boldface, for example. > > > There aren't two characters; it's unicode "combining characters" character; > is dependent upon font in use and the renderer to recognize it; if it works > it gives the effect; at worst one gets an idea of the intended effect as just > a leading/trailing underscore in plain text on older newsreaders. > > Not positive which release in T'bird it actually began to look like > underlined text; discovered it by pure accident as had used the form for some > emphasis on old groups with plain-vanilla readers "like since forever" so is > just a result of newer systems with an old and never standard habit. That doesn't answer my Q! I'm guessing that I wrap the text to be underlined in underscores same as I would asterisks to boldface; - so here goes: _this is underlined text_ -- 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-05 11:32 -0500 |
| Message-ID | <pckmbk$17tr$1@gioia.aioe.org> |
| In reply to | #110612 |
On 5/5/2018 8:50 AM, GS wrote: > No contest on the above... > > >> I've re-read the OPs ? and _guess_ I now think he probably was meaning >> retrieved from a cell and therefore already in Excel so knowing which >> way the cell is formatted answers the question. > > Exactly my point; - using a textual format eliminates the ambiguity! ... My point was that if it is formatted as a date in Excel there's never any ambiguity as to what Excel thinks it is -- the question for such as OP gave is whether the format itself is the correct one for the given input string -- and _THAT_ question is indeterminate from only the data OP provided. There's a _visual_ ambiguity to the user if they don't know whether the originator of the spreadsheet (or they forget themselves) whether the format is mm/dd or dd/mm and all there is contained in the particular cell or group of cells are month/day combinations that are in range from 1:12 so there is no other unambiguous reference. That's the question that any VBA or cell function can answer for those cells; it still can't answer the question of whether those values when entered were indeed in the one format or the other of the two choices before being input to the cell with that formatting/interpretation. Changing to mmm can remove that visual ambiguity; even that can't resolve the underlying question on the actual value, it can reflect the chosen coding of the underlying cell in a different display format. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-05 21:19 -0400 |
| Message-ID | <pcll6j$enu$1@dont-email.me> |
| In reply to | #110616 |
Ok, I'm not disputing any of what you state other than how Excel handles dates,
OR how date formats work. So here's an exercise you can do to see what I'm
saying:
Select A1:A3
While selected, key Ctrl+; followed by Ctrl+Enter
Today's date appears in numeric form 05/05/2018
Select A2 and set a Date format "14-Mar" in the Date NumberFormat list
Today's date appears in textual form 05-May
Select A3 and set its format to Text
Today's DateSerial appears 43225
Select A1:A2, type 43225 and key Ctrl+Enter
The display of A1:A2 doesn't change
Now we'll look at the system date format:
Select A1:A2
Since m/d are identical, type 5/6 then Ctrl+Enter and watch what happens. Now
type 6/5 then Ctrl+Enter into those cells. Now you see the system format for
dates displayed normally in A1 but without any ambiguity in A2.
So then, when dates are imported/pasted into cells, Excel reads the DateSerial
and renders that in the system format. Now since the numeric display has been
ambiguous to many since Vista, my solution has been to use textual formatting
instead. (That's all I'm saying) So when I design spreadsheets I deliberately
use textual date formats so there's no ambiguity (as expressed by the OP in
this thread) as to what the date is!
--
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-06 07:46 -0500 |
| Message-ID | <pcmtfu$d11$1@gioia.aioe.org> |
| In reply to | #110618 |
On 5/5/2018 8:19 PM, GS wrote: ... > So then, when dates are imported/pasted into cells, Excel reads the > DateSerial and renders that in the system format. Now since the numeric > display has been ambiguous to many since Vista, my solution has been to > use textual formatting instead. (That's all I'm saying) So when I design > spreadsheets I deliberately use textual date formats so there's no > ambiguity (as expressed by the OP in this thread) as to what the date is! It appears that in current versions you can't import two files with conflicting definitions? I wasn't aware of that; hadn't really tested presumed that if one had dates provided in a given format you could read them as the system that generated them defined them. _Way_ back, worked with a data-acq system that used dd\mm\yyyy and I surely don't recall having an issue back then (this, of course, was in DOS/Win 3 era thru _maybe_ W95). I just tried to enter as a string '5/5/2000' into two cells and format it as mm/dd/yyyy in one and dd/mm/yyyy in the other and couldn't succeed; the OS setting as you say interferes and corrupts the one; MS thinks they know better than the user it appears. I don't recall that being the case in the days of that old system...or am I just getting that senile; can't believe I wouldn't recall having to have had to do a conversion outside for the end users (albeit I was using Matlab for the work I did which reads the data and believes the user's intention is what is to be, not what it thinks it _should_ be). --
[toc] | [prev] | [next] | [standalone]
| From | dpb <none@none.net> |
|---|---|
| Date | 2018-05-06 09:37 -0500 |
| Message-ID | <pcn3ve$nee$1@gioia.aioe.org> |
| In reply to | #110619 |
On 5/6/2018 7:46 AM, dpb wrote: ... > I just tried to enter as a string '5/5/2000' into two cells and format > it as mm/dd/yyyy in one and dd/mm/yyyy in the other and couldn't > succeed; the OS setting as you say interferes and corrupts the one; MS > thinks they know better than the user it appears. ... "...tried to enter as a string '5/5/2000' into two cells" That was a typo, actually used '5/6/2000' so can see which is which from =MONTH() or =DAY() of course. --
[toc] | [prev] | [next] | [standalone]
| From | GS <gs@v.invalid> |
|---|---|
| Date | 2018-05-06 20:37 -0400 |
| Message-ID | <pco74r$529$1@dont-email.me> |
| In reply to | #110619 |
> On 5/5/2018 8:19 PM, GS wrote: > ... > >> So then, when dates are imported/pasted into cells, Excel reads the >> DateSerial and renders that in the system format. Now since the numeric >> display has been ambiguous to many since Vista, my solution has been to use >> textual formatting instead. (That's all I'm saying) So when I design >> spreadsheets I deliberately use textual date formats so there's no >> ambiguity (as expressed by the OP in this thread) as to what the date is! > > It appears that in current versions you can't import two files with > conflicting definitions? I wasn't aware of that; hadn't really tested > presumed that if one had dates provided in a given format you could read them > as the system that generated them defined them. > > _Way_ back, worked with a data-acq system that used dd\mm\yyyy and I surely > don't recall having an issue back then (this, of course, was in DOS/Win 3 era > thru _maybe_ W95). > > I just tried to enter as a string '5/5/2000' into two cells and format it as > mm/dd/yyyy in one and dd/mm/yyyy in the other and couldn't succeed; the OS > setting as you say interferes and corrupts the one; MS thinks they know > better than the user it appears. > > I don't recall that being the case in the days of that old system...or am I > just getting that senile; can't believe I wouldn't recall having to have had > to do a conversion outside for the end users (albeit I was using Matlab for > the work I did which reads the data and believes the user's intention is what > is to be, not what it thinks it _should_ be). Anything we did pre-Vista is no longer valid in terms of numeric (05/05/2018) input. It's not that Excel thinks it knows better, but rather that it reads DateSerial values rather than display values when it comes to dates. Where the problem lies for users is in [not] changing the way they enter dates in terms of d/m or m/d, depending on which OS they're working in. I was forced to figure this out after buying my 1st Win7 machine while still using my XP unit as my main machine. 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! -- 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 1 of 3 [1] 2 3 Next page →
Back to top | Article view | microsoft.public.excel.programming
csiph-web