Finding Date in an overseas format

  • Thread starter Thread starter Susan
  • Start date Start date
S

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 :)
 
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?
Click to expand...

try:
sDate = Date
That works for all language settings


Regards
Claus Busch
 
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 :)
 
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
 
Public sDate as Date

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

Susan :/
 
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).
Click to expand...

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


Regards
Claus Busch
 
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 :)
 
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.
Click to expand...

please look in VBA help for Date.


Regards
Claus Busch
 
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
 
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 :)
 
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
 
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 :)
 
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
 
Susan said:
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?
Click to expand...






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
 
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 said:
Thanks Witek! If Ben's function doesn't work, I'll try yours next.
Click to expand...

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.
 
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
 
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
 
Back
Top