Finding Date in an overseas format
 

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 :)

Finding Date in an overseas format
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Finding Date in an overseas format
 

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 :)


On Dec 22, 4:09*pm, Claus Busch wrote:
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Finding Date in an overseas format
 

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

On Dec 22, 4:09*pm, Claus Busch wrote:
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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Finding Date in an overseas format
 

Public sDate as Date

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

Susan :/


On Dec 22, 4:51*pm, Susan wrote:
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

On Dec 22, 4:09*pm, Claus Busch wrote:



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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Finding Date in an overseas format
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Finding Date in an overseas format
 

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


On Sunday, December 23, 2012 4:09:48 AM UTC-5, Claus Busch wrote:
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

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Finding Date in an overseas format
 

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 :)


On Monday, December 24, 2012 2:04:14 PM UTC-5, Susan wrote:
Ok, works for me, let's see if it works for my client! I'll let you know.

Thanks!

Susan :)





On Sunday, December 23, 2012 4:09:48 AM UTC-5, Claus Busch wrote:

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



--



Win XP PRof SP2 / Vista Ultimate SP2



Office 2003 SP2 /2007 Ultimate SP2

Finding Date in an overseas format
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Finding Date in an overseas format
 

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-tra...a1lesson14.htm

Good luck,

Ben

Finding Date in an overseas format
 

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 :)


On Wednesday, December 26, 2012 4:04:15 PM UTC-5, Ben McClave wrote:
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-tra...a1lesson14.htm



Good luck,



Ben

Finding Date in an overseas format
 

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

Finding Date in an overseas format
 

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 :)


On Thursday, December 27, 2012 5:39:53 PM UTC-5, Ben McClave wrote:
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

Finding Date in an overseas format
 

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

Finding Date in an overseas format
 

Susan wrote:
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

Finding Date in an overseas format
 

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 :)


On Wednesday, January 2, 2013 6:00:14 PM UTC-5, Ben McClave wrote:
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

Finding Date in an overseas format
 

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

Susan


On Thursday, January 3, 2013 10:14:18 PM UTC-5, witek wrote:
Susan wrote:

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

Finding Date in an overseas format
 

Susan wrote:
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.

Finding Date in an overseas format
 

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
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

Finding Date in an overseas format
 

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? I know the user in India uses "Windows Ultimate".

Thanks for all the help.
Susan


On Monday, January 7, 2013 2:30:37 AM UTC-5, Claus Busch wrote:
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

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Finding Date in an overseas format
 

On Fri, 18 Jan 2013 10:13:42 -0800 (PST), Susan wrote:

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? I know the user in India uses "Windows Ultimate".

Thanks for all the help.
Susan

Range.Find doesn't seem to work well with dates. I would avoid it.

You are going to have to loop through the cells and compare each with the date you are looking for.
If you have a lot of cells to search, you will find a significant speed advantage to reading the cells into a variant array, then looping through the array (all in VBA).

Something like Witek's idea should work.

Finding Date in an overseas format
 

Yes, Hurrah!!!!! Witek's solution worked for my Australian beta
tester, so I'm assuming it will also work for my Indian beta tester.
I did change Witek's code slightly in that I took out c.select

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

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

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

Thank you very much for all of your assistance and patience with me!
Warmly,
Susan


On Jan 18, 2:53*pm, Ron Rosenfeld wrote:
On Fri, 18 Jan 2013 10:13:42 -0800 (PST), Susan wrote:
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 "Datenot 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? *I know the user in India uses "Windows Ultimate".

Thanks for all the help.
Susan

Range.Find doesn't seem to work well with dates. *I would avoid it.

You are going to have to loop through the cells and compare each with thedateyou are looking for.
If you have a lot of cells to search, you will find a significant speed advantage to reading the cells into a variant array, then looping through the array (all in VBA).

Something like Witek's idea should work.