some problems in date entry in a web bsed statement

R

R.VENKATARAMAN

I am downloading a statement from the web. original statement was perhaps
not in excel spreadsheet format.But there is provision to save the
statement in excel spreadsheet. But the dates are ENTERED in Indian style
(also british style) i.e. date/month/year and not in excel entry mode.
the year is given as 2004 or 2003. I prepared an elaborate (perhaps not
very efficient) customisesd code for this. the part I below gives the logic
behind my code. part II gives the actual module consisting of three subs.
PART III gives sample data in the first column and the results after the vba
is run in the second column. The idea is to use excel procedures like sort,
filter, formatting etc.

apologise for the long message. what I would like to know is whether there
is any easier method without using a vba programme.
----------------------------------------------------------------------------
-----------------------------------
PART I

the original statement have many problems

1. If the date is <=12 it is in date format
I.e it is entered as m/d/y
but not as we want
12/6/03 is december 6 2003 but we want
12 june 2003. I have only to change month as date and date as month.


2.if the date is >12 it is general format. Then if you want to find
day(activecell) it is the last entry
in 25/7/03 it you type the formula
=day(activcell) it gives 3
and year(activecell) gives 2025

I have to convert day (i.e. 3) as year and add 2000. I have to convert year
as date and subtract 2000

3,. yet another conf;usion. if the date is 30 or 31
for e.g. 30/6/03
ok the day(activecell) gives 3 and I convert this to year and add 2000
but year(activecell) gives 1930(not 2030) and if I convert to date then I
have to subtract 1900
and not 200 as in the case 2 above.

4. yet another profblem the year is given as 2004 or 2003 etc. as in some
cases the year becomes dats finally this give problem so I have to replace
these into 04 or 03, after doing this I apply code <expt 4> in the code
given below.

PART II
----------------------------------------------------------------------------
-------------------------------------------
Option Explicit

Public Sub expt4()
Application.ScreenUpdating = False
Range("a1").Activate
Cells.Find(what:="Txn date").Activate
Dim myrange As Range
Set myrange = Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(2,
0).End(xlDown))
Dim cell As Range
For Each cell In myrange
cell.Activate
If ActiveCell.NumberFormat = "m/d/yy" Then
expt3
Else
expt5
End If
ActiveCell.NumberFormat = "dd-mmm-yy"
Next
Application.ScreenUpdating = True
End Sub
======================================================
Public Sub expt5()
Dim d, m, y As Integer
If Year(ActiveCell) < 2000 Then
d = Year(ActiveCell) - 1900
Else
d = Year(ActiveCell) - 2000
End If

m = Month(ActiveCell)
y = Day(ActiveCell) + 2000
ActiveCell.Offset(0, 1).Activate
ActiveCell = m & "/" & d & "/" & y


End Sub
================================================================
Public Sub expt3()
'two types of format 1) if date is <12 it is in
'date formate 2) if date is >12 itis
'general format.
'this is for those cells in date format

Dim d, m, y As Integer
Dim mo As String

d = Month(ActiveCell)
m = Day(ActiveCell)
y = Year(ActiveCell)
'mo = MonthName(m)
ActiveCell.Offset(0, 1).Activate

ActiveCell.NumberFormat = "general"

ActiveCell = m & "/" & d & "/" & y

End Sub
----------------------------------------------------------------------------
-----------
PART III

the first column is data as received, second column the result after the
code is run.

Txn Date
(empty row)
14/06/2004 14-Jun-04
29/06/2004 29-Jun-04
3/7/202004 03-Jul-04
9/7/202004 09-Jul-04
19/07/2004 19-Jul-04
21/07/2004 21-Jul-04
23/07/2004 23-Jul-04
26/07/2004 26-Jul-04
27/07/2004 27-Jul-04
28/07/2004 28-Jul-04
29/07/2004 29-Jul-04
7/8/202004 07-Aug-04
 
R

Ron Rosenfeld

I am downloading a statement from the web. original statement was perhaps
not in excel spreadsheet format.But there is provision to save the
statement in excel spreadsheet. But the dates are ENTERED in Indian style
(also british style) i.e. date/month/year and not in excel entry mode.
the year is given as 2004 or 2003. I prepared an elaborate (perhaps not
very efficient) customisesd code for this. the part I below gives the logic
behind my code. part II gives the actual module consisting of three subs.
PART III gives sample data in the first column and the results after the vba
is run in the second column. The idea is to use excel procedures like sort,
filter, formatting etc.

apologise for the long message. what I would like to know is whether there
is any easier method without using a vba programme.
----------------------------------------------------------------------------
-----------------------------------
PART I

the original statement have many problems

1. If the date is <=12 it is in date format
I.e it is entered as m/d/y
but not as we want
12/6/03 is december 6 2003 but we want
12 june 2003. I have only to change month as date and date as month.


2.if the date is >12 it is general format. Then if you want to find
day(activecell) it is the last entry
in 25/7/03 it you type the formula
=day(activcell) it gives 3
and year(activecell) gives 2025

I have to convert day (i.e. 3) as year and add 2000. I have to convert year
as date and subtract 2000

3,. yet another conf;usion. if the date is 30 or 31
for e.g. 30/6/03
ok the day(activecell) gives 3 and I convert this to year and add 2000
but year(activecell) gives 1930(not 2030) and if I convert to date then I
have to subtract 1900
and not 200 as in the case 2 above.

4. yet another profblem the year is given as 2004 or 2003 etc. as in some
cases the year becomes dats finally this give problem so I have to replace
these into 04 or 03, after doing this I apply code <expt 4> in the code
given below.

PART II
----------------------------------------------------------------------------
-------------------------------------------
Option Explicit

Public Sub expt4()
Application.ScreenUpdating = False
Range("a1").Activate
Cells.Find(what:="Txn date").Activate
Dim myrange As Range
Set myrange = Range(ActiveCell.Offset(2, 0), ActiveCell.Offset(2,
0).End(xlDown))
Dim cell As Range
For Each cell In myrange
cell.Activate
If ActiveCell.NumberFormat = "m/d/yy" Then
expt3
Else
expt5
End If
ActiveCell.NumberFormat = "dd-mmm-yy"
Next
Application.ScreenUpdating = True
End Sub
======================================================
Public Sub expt5()
Dim d, m, y As Integer
If Year(ActiveCell) < 2000 Then
d = Year(ActiveCell) - 1900
Else
d = Year(ActiveCell) - 2000
End If

m = Month(ActiveCell)
y = Day(ActiveCell) + 2000
ActiveCell.Offset(0, 1).Activate
ActiveCell = m & "/" & d & "/" & y


End Sub
================================================================
Public Sub expt3()
'two types of format 1) if date is <12 it is in
'date formate 2) if date is >12 itis
'general format.
'this is for those cells in date format

Dim d, m, y As Integer
Dim mo As String

d = Month(ActiveCell)
m = Day(ActiveCell)
y = Year(ActiveCell)
'mo = MonthName(m)
ActiveCell.Offset(0, 1).Activate

ActiveCell.NumberFormat = "general"

ActiveCell = m & "/" & d & "/" & y

End Sub
----------------------------------------------------------------------------
-----------
PART III

the first column is data as received, second column the result after the
code is run.

Txn Date
(empty row)
14/06/2004 14-Jun-04
29/06/2004 29-Jun-04
3/7/202004 03-Jul-04
9/7/202004 09-Jul-04
19/07/2004 19-Jul-04
21/07/2004 21-Jul-04
23/07/2004 23-Jul-04
26/07/2004 26-Jul-04
27/07/2004 27-Jul-04
28/07/2004 28-Jul-04
29/07/2004 29-Jul-04
7/8/202004 07-Aug-04
Click to expand...

I'm not sure what is happening here for you to obtain data received in the form
of 7/8/202004. This seems useful in that it ensures that ambiguous dates are
entered as text.

In any event, if the data as received is truly as you have written in Part III,
the following, somewhat simpler, SUB would convert and format according to what
I think are your rules:

=================
Sub ConvertDate()
Dim c As Range
Dim DateStr

For Each c In Selection
DateStr = Split(c.Text, "/")
c.Value = DateSerial(Right(DateStr(2), 4), DateStr(1), DateStr(0))
c.NumberFormat = "d-mmm-yy"
Next c

End Sub
=================


On the other hand, if your data received is in the more general format of:


07/06/03 --> 7-Jun-2003
25/08/01 --> 25-Aug-2001

and so forth, then if you can save the data as a text file, you could use the
Text-to-Columns wizard to convert it properly to dates.

There are a number of simpler ways of doing what you are doing, depending on
what the data really looks like.



--ron
 
R

R.VENKATARAMAN

sorry . there is a mistake in my copying. the date values in Part III are
only some thing like 7/8/2004 i.e. 7 august 2004.your code/alternate
procedure of using text to column seem to be very elegant. I shall try to
use it. thanks very much
 
R

Ron Rosenfeld

sorry . there is a mistake in my copying. the date values in Part III are
only some thing like 7/8/2004 i.e. 7 august 2004.your code/alternate
procedure of using text to column seem to be very elegant. I shall try to
use it. thanks very much
Click to expand...

Well, my code should work so long as the data comes over as d/m/y. But it
does no error checking. Also, if a two digit year is input, the output year
will be interpreted according to your machine settings (e.g. if it is less than
30, add 2000, otherwise add 1900.

Oh, and I think you need to have VBA6 (comes with later versions of Excel) in
order to have the Split function. If you have an earlier version, we will have
to write a small subroutine to mimic the Split function.

If the year is 2004 (and not 202004) then the code can be simplified a bit:

=========================
Sub ConvertDate()
Dim c As Range
Dim DateStr

For Each c In Selection
DateStr = Split(c.Text, "/")
c.Value = DateSerial(DateStr(2), DateStr(1), DateStr(0))
c.NumberFormat = "d-mmm-yy"
Next c
End Sub
======================


--ron
 
R

R.VENKATARAMAN

thanks. excellent. the code works pefectly.. I am having MS Excel 200 so
the VBA is VBA 6.0
I have not known about the split function. once again extremely thankful for
this elegant and short soltuin. My code was pedestrian though it worked.
 
R

Ron Rosenfeld

thanks. excellent. the code works pefectly.. I am having MS Excel 200 so
the VBA is VBA 6.0
I have not known about the split function. once again extremely thankful for
this elegant and short soltuin. My code was pedestrian though it worked.
Click to expand...

You're welcome. Thank you for the follow-up.

--ron
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Help with Date please 3
Macro in Excel 2
Split Date 8
How to count the occurrence? 3
Sumproduct with variation 1
Group date into Year and Quarters 4
Date: Week in header 4
Splitting data in a cell 2

Top