Finding Date in an overseas format

[Susan]

This macro is written in 03 VBA and saved in a 97-03 workbook. It
works fine on my XP windows desktop, and works fine on my Windows 7
Professional laptop in Office 2010 when saved as a 97-03 workbook. In
sending to an overseas client (India), however, it keeps bombing out
right at the very beginning because it cannot find the date. As you
see, I have reformulated the date in her copy to dd/mm/yy, (my
original is mm/dd/yy), because I assume her computer defaults to dd/mm/
yy. She tried manually formatting Column A to dd/mm/yy, but
apparently that hasn't worked. Any suggestions?

Option Explicit

Sub Button2_Click()

Set wbWorking = ActiveWorkbook
Set wsData = wbWorking.Worksheets("Data")
Set wsCalculations = wbWorking.Worksheets("Calculations")
Set wsMSTime = wbWorking.Worksheets("MS Time")
Set wsOtherTime = wbWorking.Worksheets("Other Time")
Set rStart = wsCalculations.Range("g5")
Set rEnd = wsCalculations.Range("g6")
Set rBoolean = wsCalculations.Range("a1")
Set rTotalTime = wsCalculations.Range("g9")

On Error GoTo OhMy

If rBoolean = 0 Then
rStart.Value = Now
rStart.NumberFormat = "hh:mm"
rEnd.Value = ""
rEnd.NumberFormat = "hh:mm"
'reset the boolean
rBoolean.Value = 1
Else
rEnd.Value = Now
rEnd.NumberFormat = "hh:mm"
'reset the boolean
rBoolean.Value = 0

'this is the end of adding the end time, now put it in sheet1.

Set rTotalTime = wsCalculations.Range("g9")

LastRow = wsData.Cells(20000, 1).End(xlUp).Row
Set rDate = wsData.Range("a3:a" & LastRow)
sDate = Format(Now, "dd/mm/yy")
Set rFound = rDate.Find(What:=sDate, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)
If rFound Is Nothing Then
MsgBox "Date not found", vbCritical
Exit Sub
End If

Thank you!
Susan

 

[Claus Busch]

Hi Susan,

Am Sat, 22 Dec 2012 12:56:51 -0800 (PST) schrieb Susan:

This macro is written in 03 VBA and saved in a 97-03 workbook. It
works fine on my XP windows desktop, and works fine on my Windows 7
Professional laptop in Office 2010 when saved as a 97-03 workbook. In
sending to an overseas client (India), however, it keeps bombing out
right at the very beginning because it cannot find the date. As you
see, I have reformulated the date in her copy to dd/mm/yy, (my
original is mm/dd/yy), because I assume her computer defaults to dd/mm/
yy. She tried manually formatting Column A to dd/mm/yy, but
apparently that hasn't worked. Any suggestions?

try:
sDate = Date
That works for all language settings


Regards
Claus Busch

 

[Susan]

Oh MY!!!! I didn't dim sDate?!

<whacks forehead>

Thank you, Claus. I will fix that. Thank goodness I included the dim
statements for you! I also changed the formatting on Column A to a
format that is not affected by individual computer settings - I don't
know if that will make any difference or not.

Susan

 

[Susan]

Whew, I'm not completely stupid... I have

Public sDate As String, sCategory As String

in another module.

I will change it to

Public sDate as Date

Susan

 

[Susan]

Public sDate as Date

doesn't work. It says it can't find the date. sDate as String worked
(on my computer).

Susan :/

 

[Claus Busch]

Hi susan,

Am Sat, 22 Dec 2012 13:54:07 -0800 (PST) schrieb Susan:

Public sDate as Date

doesn't work. It says it can't find the date. sDate as String worked
(on my computer).

what I meant is to change
sDate = Format(Now, "dd/mm/yy")
to
sDate =Date


Regards
Claus Busch

 

[Susan]

Ok, works for me, let's see if it works for my client! I'll let you know.
Thanks!
Susan

 

[Susan]

As I said, it works for me, but my client in India is still receiving the error message "Date not found".

Column A was formatted as mm/dd/yyyy, in a format that will adjust to her computer settings, and the line sDate = Format(Now, "dd/mm/yy") was changed to sDate = Date.

Any other suggestions? She is going to contact her computer "guy", but I don't know how familiar he is with VBA, if at all.

Gratefully,
Susan

 

[Claus Busch]

Hi Susan,

Am Wed, 26 Dec 2012 10:31:41 -0800 (PST) schrieb Susan:

As I said, it works for me, but my client in India is still receiving the error message "Date not found".

Column A was formatted as mm/dd/yyyy, in a format that will adjust to her computer settings, and the line sDate = Format(Now, "dd/mm/yy") was changed to sDate = Date.

Any other suggestions? She is going to contact her computer "guy", but I don't know how familiar he is with VBA, if at all.

please look in VBA help for Date.


Regards
Claus Busch

 

[Ben McClave]

Susan,

Have you tried DateSerial yet? The syntax is DateSerial(Year, Month, Day), and the result is independent of the system's date format settings. Using the Date function to return today's date, you could try:

sDate = DateSerial(Year(Date), Month(Date), Day(Date))

In addition, I found that the following website had some helpful tips about working with dates in Excel:

http://www.ozgrid.com/Excel/free-training/ExcelVBA1/excelvba1lesson14.htm

Good luck,

Ben

 

[Susan]

Thank you, Ben. I knew about a "date" being an actual serial number, but I didn't know about DateSerial and its benefits for overseas use.

I read the OzGrid post you suggested. I had one small question - does it need to be formatted in some manner, or as you posted it, will it automatically sort itself out in whatever date format her Column A automatically formatted itself in?

Thank you!
Susan

 

[Ben McClave]

Susan,

I'm not 100% sure about the question, since I haven't tried it with different settings. However, when I typed the function in my Immediate Window, the result was formatted the way the rest of my workbook is. In other words:

typing:
print dateserial(year(date), month(date), day(Date))
in the Immediate Window returned:
12/27/2012

Ben

 

[Susan]

Thank you Ben!

Unfortunately my client in India reports today that she is still seeing the same error message: Date not found. Even with sDate = DateSerial(Year(Date), Month(Date), Day(Date)).

Any other ideas?

Susan

 

[Ben McClave]

Susan,

Since you are ultimately trying to determine if there is a matching date in a certain range, perhaps a Function will work as just as well. For example, using the Function at the bottom of this post, you could replace these lines from your existing code:

sDate = Format(Now, "dd/mm/yy")
Set rFound = rDate.Find(What:=sDate, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
MatchCase:=False)

with this line (which references the Function below):

Set fFound = FindDate(rDate, Date)

Note that you may change the "Date" at the end of this function call to whatever date you wish. The Function "FindDate" will return a range of cells where the date matches the date variable fed to the Function:

Function FindDate(rDate As Range, dFind As Date) As Range
Dim rCell As Range

For Each rCell In rDate
If DateValue(CDate(rCell.Value)) = dFind Then
If FindDate Is Nothing Then
Set FindDate = rCell
Else
Set FindDate = Union(FindDate, rCell)
End If
End If
Next rCell

End Function

Good Luck,

Ben

 

[witek]

Thank you Ben!

Unfortunately my client in India reports today that she is still seeing the same error message: Date not found. Even with sDate = DateSerial(Year(Date), Month(Date), Day(Date)).

Any other ideas?

Set rDate = wsData.Range("a3:a" & LastRow)


Dim iDate As Long
iDate = Fix(Now())

Dim rFound As Range

Dim c As Range
For Each c In rDate
If c.Value2 = iDate Then
Set rFound = c
c.Select
Exit For
End If
Next c

If rFound Is Nothing Then
MsgBox "Date not found", vbCritical
Exit Sub
End If

 

[Susan]

Ok, Ben, thank you very much. It works in my spreadsheet, but then again so has everything else! I'll send it along to India and see if it works there.

I greatly appreciate your help.
Susan

 

[Susan]

Thanks Witek! If Ben's function doesn't work, I'll try yours next.

Susan

 

[witek]

Thanks Witek! If Ben's function doesn't work, I'll try yours next.

It is format independent. It uses internal representation of date.
It can be slower then Excel's built-in function because you must loop
through all cells but it works with any date format.

it can be done faster if you read range into array variable before looping.

the other fast solution is to write it in C++ as addin.

 

[Claus Busch]

Hi Susan,

so all solutions don't work I think the values in the india file *looks*
like dates but they are *strings*


Regards
Claus Busch

 

[Susan]

Nope, they are dates. What looks like 12/31/2012, when formatted as "general", shows 41274.

Every single solution provided works for me, here in the US, and for other beta testers in the US. I also sent the spreadsheet to another user in Australia, and he comes up with the same "Date not found" error message. So I've ruled out a problem with the India user's individual computer.

Haven't tried Witek's offering yet - that's next. Will report back.

Could it possibly be something to do with their non-standard Windows OS? Iknow the user in India uses "Windows Ultimate".

Thanks for all the help.
Susan