Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old May 4th 18, 08:31 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2013
Posts: 35
Default 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

  #5   Report Post  
Old May 4th 18, 10:37 PM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 937
Default 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   Report Post  
Old May 5th 18, 12:49 AM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 60
Default 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   Report Post  
Old May 5th 18, 02:42 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 937
Default 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   Report Post  
Old May 5th 18, 05:46 AM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 60
Default 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   Report Post  
Old May 5th 18, 09:21 AM posted to microsoft.public.excel.programming
external usenet poster
 
First recorded activity by ExcelBanter: Apr 2015
Posts: 937
Default 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   Report Post  
Old May 5th 18, 01:21 PM posted to microsoft.public.excel.programming
dpb dpb is offline
external usenet poster
 
First recorded activity by ExcelBanter: Jan 2018
Posts: 60
Default 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.

--


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Check for duplicate numbers from ones entered and anoter set Goldie Excel Programming 1 May 29th 09 08:47 AM
Dates - Need to display date one month prior to user-entered date brettopp Excel Worksheet Functions 13 December 3rd 07 05:58 PM
Date subtraction -How to not show negative when 2nd date not entered Edward[_2_] New Users to Excel 2 September 27th 07 03:03 PM
Check Mark appears when a letter is entered in a field Gina Excel Programming 2 February 22nd 07 08:23 PM
Macro to check if data has been entered Mike R. Excel Programming 1 December 20th 04 11:14 PM


All times are GMT +1. The time now is 05:24 AM.

Powered by vBulletin® Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Copyright © 2017