VBA Sub procedures
 

Hi,

I am a newbie to VBA.
Can any one help me in answering this question?

(Q4)
The following worksheet contains some dates in cells A2:A9.

Date New Display Leap Year
2/5/2004
28/09/2000
12/8/2001
23/07/1976
31/12/1836
15/03/2084
8/10/2800
22/02/1736

Write a sub procedure to perform the following operations:
(a) Display the dates in the column B in the form, for exampl
02/ May/2004,
(b) Indicate whether the year is a leap year in column C wit
Yes or No.

Conditions for a year to be a leap year:
A year is a leap year, if it is divisible by 4;
But, it is not a leap year, if it is divisible b
100;
However, it is a leap year, if it is divisibl
by 400


Thank You Very Much!
Lexie

--
lexie
-----------------------------------------------------------------------
lexiez's Profile: http://www.excelforum.com/member.php...fo&userid=1498
View this thread: http://www.excelforum.com/showthread.php?threadid=26603

VBA Sub procedures
 

Excel cannot work properly with dates before 1/1/1900.
VBA can.. so we can circumvent it with a few userdefined functions.

create a module in your workbook
copy following code...

Function DateReformat(s As String)
If IsDate(s) Then
DateReformat = Format(DateValue(s), "dd\/mm\/yyyy")
ElseIf s = vbNullString Then
DateReformat = Empty
Else
DateReformat = CVErr(xlErrValue)
End If
End Function

Function LeapYear(s As String)
If IsDate(s) Then
LeapYear = Day(DateSerial(Year(DateValue(s)), 2, 29)) = 29
ElseIf s = vbNullString Then
LeapYear = Empty
Else
LeapYear = CVErr(xlErrValue)
End If
End Function


Now in B2 type = DateReformat(A2)
Now in C2 type = LeapYear(A2)

and copy down..







keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


lexiez wrote:

Date New Display Leap Year
2/5/2004
28/09/2000
12/8/2001
23/07/1976
31/12/1836
15/03/2084
8/10/2800
22/02/1736

VBA Sub procedures
 

Hi, keepITcool,
Instead of: DateReformat = Format(DateValue(s), "dd\/mm\/yyyy")
perhaps : DateReformat = Format(DateValue(s), "dd\/mmm\/yyyy")
Jorge
"keepITcool" escreveu na mensagem
...

Excel cannot work properly with dates before 1/1/1900.
VBA can.. so we can circumvent it with a few userdefined functions.

create a module in your workbook
copy following code...

Function DateReformat(s As String)
If IsDate(s) Then
DateReformat = Format(DateValue(s), "dd\/mm\/yyyy")
ElseIf s = vbNullString Then
DateReformat = Empty
Else
DateReformat = CVErr(xlErrValue)
End If
End Function

Function LeapYear(s As String)
If IsDate(s) Then
LeapYear = Day(DateSerial(Year(DateValue(s)), 2, 29)) = 29
ElseIf s = vbNullString Then
LeapYear = Empty
Else
LeapYear = CVErr(xlErrValue)
End If
End Function


Now in B2 type = DateReformat(A2)
Now in C2 type = LeapYear(A2)

and copy down..







keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


lexiez wrote:

Date New Display Leap Year
2/5/2004
28/09/2000
12/8/2001
23/07/1976
31/12/1836
15/03/2084
8/10/2800
22/02/1736