Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

US/UK Date conflict

  Asked By: Lorraine    Date: Mar 01    Category: MS Office    Views: 980
  

I am very new to VBA and may have thrown myself into the deep end with my
first bash.

I have created a simple Excel file of athletes (to be used later in a Lotus
Approach Database [sorry MS]). If an athletes data is missing I have attempted
to create a macro to enable it to be entered, with some of the data being
provided by the macro - SeasonDate, JuniorDate.

The main issue I am having is with the user entered DateOfBirth. When
entered it is used to calculate two more values - SeasonAge and JnrSeasonAge. I
have declared the various dates as <Date> it would appear that I am having a
conflict between US dates (mm/dd/yyyy) and UK dates (dd/mm/yyyy). All is ok
when
the date is clearly non-US (e.g. 13th day...) but when there is a
possibility of either date being valid it prefers to convert it to US?

I have pasted the subject macro below.




Private Sub cmbOK_Click()
Dim varSeasonAge As Double
Dim varJnrSeasonAge As Double
Dim varSeasonDate As Date
Dim varJuniorDate As Date
Dim varDateOfBirth As Date

varSeasonDate = "01/09/2006"
varJuniorDate = "01/01/2007"

ActiveWorkbook.Sheets("Athletes").Activate
Range("A3").Select
Do
If IsEmpty(activecell) = False Then
activecell.Offset(1, 0).Select
End If
Loop Until IsEmpty(activecell) = True
activecell.Offset(0, 1) = txbAthleteID.Value
activecell.Offset(0, 2) = txbSurname.Value
activecell.Offset(0, 3) = txbForeName.Value
activecell.Offset(0, 4) = txbDateofBirth.Value
Selection.Value = Selection.Offset(0, 3).Value & " " & Selection.Offset(0, 2)
activecell.Offset(0, 8) = varJuniorDate
activecell.Offset(0, 9) = varSeasonDate

If activecell.Offset(0, 4) = "" Then
activecell.Offset(0, 5).Value = "No DoB"
activecell.Offset(0, 6).Value = "No DoB"
activecell.Offset(0, 7).Value = "No DoB"
ElseIf activecell.Offset(0, 4) <> "" Then
varDateOfBirth = activecell.Offset(0, 4)
activecell.Offset(0, 5).Value = (varSeasonDate - varDateOfBirth) / 365.31
activecell.Offset(0, 6).Value = (varJuniorDate - varDateOfBirth) / 365.31
End If

varSeasonAge = activecell.Offset(0, 5).Value
varJnrSeasonAge = activecell.Offset(0, 6).Value

If varSeasonAge < 13 Then
activecell.Offset(0, 7).Value = "U13"
ElseIf varSeasonAge < 15 Then
activecell.Offset(0, 7).Value = "U15"
ElseIf varSeasonAge < 17 Then
activecell.Offset(0, 7).Value = "U17"
ElseIf varJnrSeasonAge < 20 Then
activecell.Offset(0, 7).Value = "J"
ElseIf varSeasonAge < 95 Then
activecell.Offset(0, 7).Value = "S"

End If
End Sub

Share: 

 

6 Answers Found

 
Answer #1    Answered By: Gina Tanaka     Answered On: Mar 01

Sets to UK format
varJuniorDate = Format ( "DD/MM/YYYY")"

... on how to address the issue  I have encountered the following errors:


Not sure if I have installed the format correctly (see below) but comes up
with an 'Overflow' error (6) when run?

Private Sub cmbOK_Click()
Dim varSeasonAge As Double
Dim varJnrSeasonAge As Double
Dim varSeasonDate As Date
Dim varJuniorDate As Date
Dim varDateOfBirth As Date

varSeasonDate = Format(DD / MM / YYYY)
varJuniorDate = Format(DD / MM / YYYY)
varDateOfBirth = Format(DD / MM / YYYY)

varSeasonDate = "01/09/2006"
varJuniorDate = "01/01/2007"

Although I have specified a 'Format' template for all three possible Date
fields it is the DateOfBirth that is causing the problem. May be a red herring?


When I put the Format template in quotes ("") it came up with a 'Type
mismatch' error 13?

varSeasonDate = Format("DD/MM/YYYY")
varJuniorDate = Format("DD/MM/YYYY")
varDateOfBirth = Format("DD/MM/YYYY")

Again, being very new to VBA I may not be fully grasping where I should have
entered the suggested code.

 
Answer #2    Answered By: Ibadah Younis     Answered On: Mar 01

Sets to UK format
varJuniorDate = Format ( "DD/MM/YYYY")

... on how to address the issue  I have encountered the following errors:

Not sure if I have installed the format correctly (see below) but comes up
with an 'Overflow' error (6) when run?

Private Sub cmbOK_Click()
Dim varSeasonAge As Double
Dim varJnrSeasonAge As Double
Dim varSeasonDate As Date
Dim varJuniorDate As Date
Dim varDateOfBirth As Date

varSeasonDate = Format(DD / MM / YYYY)
varJuniorDate = Format(DD / MM / YYYY)
varDateOfBirth = Format(DD / MM / YYYY)

varSeasonDate = "01/09/2006"
varJuniorDate = "01/01/2007"

Although I have specified a 'Format' template for all three possible Date
fields it is the DateOfBirth that is causing the problem. May be a red
herring?

When I put the Format template in quotes ("") it came up with a 'Type
mismatch' error 13?

varSeasonDate = Format("DD/MM/varSea
varJuniorDate = Format("DD/MM/YYYY")
varDateOfBirth = Format("DD/MM/YYYY")

Again, being very new to VBA I may not be fully grasping where I should have
entered the suggested code.

 
Answer #3    Answered By: William Evans     Answered On: Mar 01

I think that there’s something wrong with your email as it seems to have
truncated lines in it.

The FORMAT function needs something to format.
The syntax is well documented in the help and is...

StringVariable = Format(expression[, format[, firstdayofweek[,
firstweekofyear]]])

Where the expresion is a gives a date  and the format is a string.

For example StringVariable=Format(Now(),"dd/mm/yy")

... Will return the current date as, for example, "01/05/07" for the 1st of
March.

You can also use string variables for the format part and format numbers and
strings as well as dates.

To "adjust" part of your code ....
> Private Sub cmbOK_Click(P
> Dim varSeasonAge As Date
> Dim varJnrSeasonAge As Date
> Dim varSeasonDate As String
> Dim varJuniorDate As String
> Dim varDateOfBirth As String
>
> Dim varResultDate as string
>
>
> varSeasonDate = "DD / MM / YYYY)"
> varJuniorDate = "DD / MM / YYYY"
> varDateOfBirth = “DD / MM / YYYY”
>
> varSeasonDate = #01/09/2006#
> varJuniorDate = #01/01/2007#
>
> varResultDate = Format(varSeasonDate ,varSeasonDate)
> varResultDate = Format(varJuniorDate ,varJuniorDate)

Note that the function returns a string and needs a string for the “pattern”
and also that dates  are entered  with hashes around them. I’ve not tested the
above but I’m sure it will work.

What are you trying to actually do again please?

 
Answer #4    Answered By: Audris Schmidt     Answered On: Mar 01

I have not answered because the stuff you have posted does not make any sense
to me. I hoped someone else would understand it and help.

The line

varDateOfBirth = Format(DD / MM / YYYY)

Is quite meaningless AFAICS.

The Format function takes an expression and returns a string. You have
defined varDateOfBirth as a date. So you cannot hand it a string. If it was a
variant (which would be the normal reason for starting it with "var") it could
be given a string.

If x was a date, say 31 December 2001 and you used the format function you
could use it like this

Y=format(x,"yy/mm/dd") and y would then hold "01/12/31"

I doi not think this is what you need at all.

I think you need to know what form your input is in. I suspect it is strings
in the form "dd/mm/yyyyy". If so the best bet is to pull each date  apart and
reassemble them to get a julian (A number representing the date)

Make a function Like this:

Function ConvertDate(DateString As Variant) As Date

Dim DateBit, MonthPart, YearPart


DateBit = CInt(Left(DateString, 2))
MonthPart = CInt(mid(DateString, 4, 2))
YearPart = CInt(Right(DateString, 4))

ConvertDate = DateSerial(YearPart, MonthPart, DateBit)

End Function

Then in your main  code you would read the date as a string to a variable, say
strDate

Then you would have a line that said

varDateOfBirth = ConvertDate(strDate)

Now varDateOfBirth contains the correct date and you can use it.

Please let us know how you get on.

I have assumed that your regional settings on your computer are set to read
dates formatted the US way. If not, there will need to be a bit of tweaking
of the code.

 
Answer #5    Answered By: Rory Anderson     Answered On: Mar 01

You mentioned you were new to VBA. Perhaps you don’t yet understand how the
Format function works.



The Format function is used to take a value and convert  it to a specified
String representation. So if you have a date  variable:



Dim varDateOfBirth Ad Date



And if you set its value to 30 April 2007 like this (in the US):



varDateOfBirth = “4/30/2007”



And you want to convert it to a String so you can display it to the user,
and have this string in a certain format, you can use the Format function:



Dim strDateOfBirth as String

strDateOfBirth = Format(varDateOfBirth, “mm/dd/yyyy”)



In this specific case, this will make sure it prints out with two digits for
both the month and day, like “04/30/2007” instead of just “4/30/2007”.



By the way, it is more customary to use “var” as a variable name prefix if
the variable is of type Variant, like this:



Dim varDateOfBirth As Variant



It would be more common for a Date to use the prefix dt or dat like this:



Dim dtDateOfBirth As Date

Dim datDateOfBirth As Date



Additionally, double quotes are the standard delimiters for Strings, and
they work for Dates, but it is more common for Dates to use hash symbols, as
in:



dtDateOfBirth = #4/30/2007#

instead of

dtDateOfBirth = “4/30/2007”



You do not use the Format function to specify a formatting template, as you
said, that you can apply to a variable once and think it stays that way –
the Format function generates a String value of a specified format when you
call it – that one time.



Also, you mentioned in your Subject something about the UK. If you prefer
the day  to come before the month, you can use:



strDateOfBirth = Format(dtDateOfBirth, “dd/mm/yyyy”)



There are also predefined date formats you can make use of that would
automatically adjust based on your computer’s locale setting so the same
code could be used in the US or the UK without any code changes.

 
Answer #6    Answered By: Scott Anderson     Answered On: Mar 01

Other commitments prevent me from testing the various
ideas just yet. I thought it important to acknowledge the various responses
and that I will get back to you all when I am able to.

 
Didn't find what you were looking for? Find more on US/UK Date conflict Or get search suggestion and latest updates.




Tagged: