Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
dpb wrote:
On 5/4/2018 2:31 AM, 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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/4/2018 10:55 AM, Auric__ wrote:
dpb wrote: On 5/4/2018 2:31 AM, 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. -- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/4/2018 10:55 AM, Auric__ wrote:
dpb wrote: On 5/4/2018 2:31 AM, 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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. -- |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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. -- |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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. -- |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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. -- |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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. -- |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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. -- |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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). -- |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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. -- |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#21
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/6/2018 7:37 PM, GS wrote:
.... So docs done on pre-Vista systems would still show correct dates when opened in the later OSs because regardless of how the system format displays, the DateSerial dictates the actual date value! That's ok for stuff already _in_ MS product; what about the question of importing data from external systems that may have different encoding between them? Looks like that's broken to me... -- |
#22
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/6/2018 7:37 PM, GS wrote:
... So docs done on pre-Vista systems would still show correct dates when opened in the later OSs because regardless of how the system format displays, the DateSerial dictates the actual date value! That's ok for stuff already _in_ MS product; what about the question of importing data from external systems that may have different encoding between them? Looks like that's broken to me... Hmm.., I guess you're talking about dbase date formats and AFAIK these also use DateSerial; - but then that only applies to MS-based or Windows-based dbases. I haven't run into any issues using generic dbases like SQLite, though, but then never did the XP/Vista+ testing thing with that. I do know the dates are correct in my stuff because I set the format to always use the 3-char month abreviation. (Suggests date values univerally use DateSerials to be cross-OS compatible!) -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#23
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/6/2018 8:03 PM, GS wrote:
On 5/6/2018 7:37 PM, GS wrote: ... So docs done on pre-Vista systems would still show correct dates when opened in the later OSs because regardless of how the system format displays, the DateSerial dictates the actual date value! That's ok for stuff already _in_ MS product; what about the question of importing data from external systems that may have different encoding between them?* Looks like that's broken to me... Hmm.., I guess you're talking about dbase date formats and AFAIK these also use DateSerial; - but then that only applies to MS-based or Windows-based dbases. I haven't run into any issues using generic dbases like SQLite, though, but then never did the XP/Vista+ testing thing with that. I do know the dates are correct in my stuff because I set the format to always use the 3-char month abreviation. (Suggests date values univerally use DateSerials to be cross-OS compatible!) No, the issue of text from two disparate systems; one encoded as mm/dd/yyyy and the other dd/mm/yyyy. AFAICT, there's no way other than to have to recode to match the system default to get the one not matching to be interpreted correctly. That's just rude... -- |
#24
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/6/2018 8:03 PM, GS wrote:
On 5/6/2018 7:37 PM, GS wrote: ... So docs done on pre-Vista systems would still show correct dates when opened in the later OSs because regardless of how the system format displays, the DateSerial dictates the actual date value! That's ok for stuff already _in_ MS product; what about the question of importing data from external systems that may have different encoding between them?* Looks like that's broken to me... Hmm.., I guess you're talking about dbase date formats and AFAIK these also use DateSerial; - but then that only applies to MS-based or Windows-based dbases. I haven't run into any issues using generic dbases like SQLite, though, but then never did the XP/Vista+ testing thing with that. I do know the dates are correct in my stuff because I set the format to always use the 3-char month abreviation. (Suggests date values univerally use DateSerials to be cross-OS compatible!) No, the issue of text from two disparate systems; one encoded as mm/dd/yyyy and the other dd/mm/yyyy. AFAICT, there's no way other than to have to recode to match the system default to get the one not matching to be interpreted correctly. That's just rude... Actually, your example isn't textual dates; - they're numeric! This is where the problem lies. A2 in my exercise is an example of textual date format; - it doesn't matter what the system format is because that format will ALWAYS display correctly. -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#25
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#26
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#27
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/7/2018 2:14 AM, GS wrote:
Actually, your example isn't textual dates; - they're numeric! This is where the problem lies. A2 in my exercise is an example of textual date format; - it doesn't matter what the system format is because that format will ALWAYS display correctly. It seems like the text ' confused Excel and my little-used Excel "skills" led me down the primrose path...I forgot that 5/5 isn't interpreted as division if don't use the preceding '=' so was trying too hard to force interpretation as date. Using just 5/6 or whatever does get interpreted correctly and one can use a custom format of d/m/yy _or_ m/d/yy OK and mix them; all is well after all...sorry for my miscue on the data entry. The point still is, though, that your example all starts with the date form being known a priori and all the example does is use a non-ambiguous visual format to display the content. There still is no way to determine which of two ambiguous date forms from another system _AS THE TEXT DATE STRING_ is which from the string format alone; and it still isn't totally clear for OP's problem after the explanation whether he has the required information at the point he's trying to solve the problem or not. What I've discovered is that you can still manually force the cell format to interpret the external input correctly by applying the appropriate format but the initial input will be interpreted based on the system setting. I'm used to being able to use MATLAB input forms in which I can specifically define that the input format is what I want irrespective of the system settings. -- |
#28
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
|
#29
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/7/2018 2:14 AM, GS wrote:
Actually, your example isn't textual dates; - they're numeric! This is where the problem lies. A2 in my exercise is an example of textual date format; - it doesn't matter what the system format is because that format will ALWAYS display correctly. It seems like the text ' confused Excel and my little-used Excel "skills" led me down the primrose path...I forgot that 5/5 isn't interpreted as division if don't use the preceding '=' so was trying too hard to force interpretation as date. Using just 5/6 or whatever does get interpreted correctly and one can use a custom format of d/m/yy _or_ m/d/yy OK and mix them; all is well after all...sorry for my miscue on the data entry. The point still is, though, that your example all starts with the date form being known a priori and all the example does is use a non-ambiguous visual format to display the content. There still is no way to determine which of two ambiguous date forms from another system _AS THE TEXT DATE STRING_ is which from the string format alone; and it still isn't totally clear for OP's problem after the explanation whether he has the required information at the point he's trying to solve the problem or not. What I've discovered is that you can still manually force the cell format to interpret the external input correctly by applying the appropriate format but the initial input will be interpreted based on the system setting. I'm used to being able to use MATLAB input forms in which I can specifically define that the input format is what I want irrespective of the system settings. If the source data is indeed StringText then you're at the mercy of Excel interpreting as per system date format and the resulting ambiguity. Using textual date formats ("May 05, 2018") will ALWAYS display correctly because Excel will indeed treat them as text. (ergo not useable in formulas by direct ref to the cell) If the source data uses date formats then what displays is a DateSerial in the chosen format. In this case Excel will use the DateSerial and render it in the format of its target cell. All is well! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#30
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
"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 |
#31
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#32
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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. -- |
#33
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/7/2018 12:06 PM, GS wrote:
.... If the source data is indeed StringText then you're at the mercy of Excel interpreting as per system date format and the resulting ambiguity. Using textual date formats ("May 05, 2018") will ALWAYS display correctly because Excel will indeed treat them as text. (ergo not useable in formulas by direct ref to the cell) .... Yes, but sometimes with foreign systems one doesn't have the ability to change the output form...sad, but ime, more often than one would think, still all too true for specialty systems from a hardware vendor or the like that are just simple prepackaged demos of how to use the system but also ime, probably 70-80% of the clients would never have written a better tool for their purpose but instead just make do with the toy sample from the vendor and live with the warts. Been burnt too many times.... :( -- |
#34
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 8-May-2018 6:53 am, dpb wrote:
On 5/7/2018 12:06 PM, GS wrote: ... If the source data is indeed StringText then you're at the mercy of Excel interpreting as per system date format and the resulting ambiguity. Using textual date formats ("May 05, 2018") will ALWAYS display correctly because Excel will indeed treat them as text. (ergo not useable in formulas by direct ref to the cell) ... Yes, but sometimes with foreign systems one doesn't have the ability to change the output form...sad, but ime, more often than one would think, still all too true for specialty systems from a hardware vendor or the like that are just simple prepackaged demos of how to use the system but also ime, probably 70-80% of the clients would never have written a better tool for their purpose but instead just make do with the toy sample from the vendor and live with the warts. Been burnt too many times.... :( -- slightly off topic.. I've been following this thread and have learned things that will help when I encounter date problems in excel and VBA which I often do. But what amazes me is that we persist in using these two date formats - d/m/y and m/d/y. It seems absurd we accept conventions that are blatantly so ambiguous! And, especially, their continuing use by software engineers who are expected to think logically. I'm always coming across applications or data where I have to struggle to work out which is being used. d/m/y and m/d/y are not formally internationally accepted date formats and I'm surprised that there hasn't been a trend towards a more rational format, such as yyyy-mmm-dd. The very least is a format where the elements have increasing or decreasing significance - unlike m/d/y which is neither. |
#35
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 5/7/2018 3:19 PM, malone wrote:
.... But what amazes me is that we persist in using these two date formats - d/m/y and m/d/y. It seems absurd we accept conventions that are blatantly so ambiguous! And, especially, their continuing use by software engineers who are expected to think logically. I'm always coming across applications or data where I have to struggle to work out which is being used. d/m/y and m/d/y are not formally internationally accepted date formats and I'm surprised that there hasn't been a trend towards a more rational format, such as yyyy-mmm-dd. The very least is a format where the elements have increasing or decreasing significance - unlike m/d/y which is neither. No, but we 'Murricuns aren't much given to having others tell us what to do; note the similar reluctance for widespread adoption of metric for common use; after nearly 40 yr since was a widespread attempt to enforce, it's still gotten essentially nowhere outside industrial and scientific circles and I suspect another 40 will be about the same. Date convention in US is a similarly ingrown habit; that there are coding standards is pretty-much immaterial to the casual user and I'd venture 70-80% of the spreadsheets in use are done by just that type of individual; may be pretty adept at the usage of Excel interface and all, but little or no actual coding training so they continue to just use everyday convention. As noted in an earlier response, in a 40-yr+/- consulting career in mostly data acq and instrumentation for power utilities I saw a hundred or more data acq systems put together from probably over half that many vendors and every conceivable form and some that were truly incredible for encoding date and time was in the sample set I think. As noted, even vendors would (and I'm sure still do) provide sample code most probably put together by the summer intern that would think something as sophisticated of deliberately using the sequence order of d/m/y as almost revolutionary and, probably, "European". Teaching those folks about serial numbers is a struggle that one rarely wins. As noted, Matlab has a class |datetime| and has enhanced the C i/o format string to read date strings such that one can on a case-by-case basis handle it on input. MS tends to also be heavy-handed in they code as if everything is MS-consistent and leave it up to the user to conform or write the glue to interact. -- |
#36
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
On 8-May-2018 6:53 am, dpb wrote:
On 5/7/2018 12:06 PM, GS wrote: ... If the source data is indeed StringText then you're at the mercy of Excel interpreting as per system date format and the resulting ambiguity. Using textual date formats ("May 05, 2018") will ALWAYS display correctly because Excel will indeed treat them as text. (ergo not useable in formulas by direct ref to the cell) ... Yes, but sometimes with foreign systems one doesn't have the ability to change the output form...sad, but ime, more often than one would think, still all too true for specialty systems from a hardware vendor or the like that are just simple prepackaged demos of how to use the system but also ime, probably 70-80% of the clients would never have written a better tool for their purpose but instead just make do with the toy sample from the vendor and live with the warts. Been burnt too many times.... :( -- slightly off topic.. I've been following this thread and have learned things that will help when I encounter date problems in excel and VBA which I often do. But what amazes me is that we persist in using these two date formats - d/m/y and m/d/y. It seems absurd we accept conventions that are blatantly so ambiguous! And, especially, their continuing use by software engineers who are expected to think logically. I'm always coming across applications or data where I have to struggle to work out which is being used. d/m/y and m/d/y are not formally internationally accepted date formats and I'm surprised that there hasn't been a trend towards a more rational format, such as yyyy-mmm-dd. The very least is a format where the elements have increasing or decreasing significance - unlike m/d/y which is neither. Well stated! My preference is yyyy-mmm-dd (1st) or mmm-dd-yyyy (2nd) for legal purposes, otherwise mmm-dd or dd-mmm for storing date values. In all cases the 3 char month abbreviation is used. Using yyyy in the 1st element position (yyyy-mm-dd) just makes sense for sorting purposes, IMO:) Thankfully, most development languages use DateSerial and offer text formatting options as to how the developer wants that to be displayed. But as you state, I can't understand why they persist to use an ambiguous format; - after all, it's not like they don't know how systems they develop for work! -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#37
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
#38
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. :) 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. -- |
#39
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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. -- |
#40
Posted to microsoft.public.excel.programming
|
|||
|
|||
Check how date is entered
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 |
Reply |
|
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Check for duplicate numbers from ones entered and anoter set | Excel Programming | |||
Dates - Need to display date one month prior to user-entered date | Excel Worksheet Functions | |||
Date subtraction -How to not show negative when 2nd date not entered | New Users to Excel | |||
Check Mark appears when a letter is entered in a field | Excel Programming | |||
Macro to check if data has been entered | Excel Programming |