|
First Monday Of The Year (YearStart)
The following function was provided by John Green, an Excel MVP from Australia.
It returns the first Monday of the specified year. This function is used by other
functions on this page.
Public Function YearStart(WhichYear As Integer) As Date
Dim WeekDay As Integer
Dim NewYear As Date
NewYear = DateSerial(WhichYear, 1, 1)
WeekDay = (NewYear - 2) Mod 7 'Generate weekday index where Monday = 0
If WeekDay < 4 Then
YearStart = NewYear - WeekDay
Else
YearStart = NewYear - WeekDay + 7
End If
End Function
Monday Of A Given Week Number (WeekStart)
This function returns the date of the Monday in a given week number, e.g., the Monday
of the 15th week of 1998. It requires the YearStart function, given
above.
Public Function WeekStart(WhichWeek As Integer, WhichYear As _
Integer) As Date
WeekStart = YearStart(WhichYear) + ((WhichWeek - 1) * 7)
End Function
IsLeapYear
This function returns TRUE if the specified year is a leap year, or
FALSE if it is not.
Public Function IsLeapYear(Y As Integer)
IsLeapYear = Month(DateSerial(Y, 2, 29)) = 2
End Function
Military To Time
This function converts military time, supplied as an integer, to a standard serial time
number. For example, it converts 1130 to 11:30, and 1650 to 16:50.
Public Function MilitaryToTime(T1 As Integer)
'
' Input T1: 24-hour time as integer,e.g., 1130=11:30, 1650=16:50
' Output, time as serial time e.g, 0.5 for noon.
'
Dim TT1 As Double
TT1 = Int(T1 / 100) + (((T1 / 100) - Int(T1 / 100)) / 0.6) '23.50
TT1 = TT1 / 24
MilitaryToTime = TT1
End Function
See the Date Time Entry page for some
code to allow you to enter dates and times in this format, and have the
converted automatically on entry to real Excel dates and times.
Nth Day Of Week
This function will return will return the date of Nth day-of-week for a given month and
year. For example, it will return 26-March-98 for the 4th Thursday of March, 1998.
Days-of-week range from 1 to 7, with Sunday = 1 and Saturday = 7.
Public Function NthDayOfWeek(Y As Integer, M As Integer,
_
N As
Integer, DOW As Integer) As Date
NthDayOfWeek = DateSerial(Y, M, (8 - WeekDay(DateSerial(Y, M, 1),
_
(DOW + 1) Mod 8)) + ((N
- 1) * 7))
End Function
Week Number (ISOWeekNum)
The following function was provided by John Green, an Excel MVP from Australia.
It returns the first week number of the specified year. It requires the YearStart
function, given earlier.
Public Function ISOWeekNum(AnyDate As Date, Optional
WhichFormat As Variant) As Integer
' WhichFormat: missing or <> 2 then returns week number,
'
= 2 then YYWW
'
Dim ThisYear As Integer
Dim PreviousYearStart As Date
Dim ThisYearStart As Date
Dim NextYearStart As Date
Dim YearNum As Integer
ThisYear = Year(AnyDate)
ThisYearStart = YearStart(ThisYear)
PreviousYearStart = YearStart(ThisYear - 1)
NextYearStart = YearStart(ThisYear + 1)
Select Case AnyDate
Case Is >= NextYearStart
ISOWeekNum = (AnyDate - NextYearStart) \ 7 + 1
YearNum = Year(AnyDate) + 1
Case Is < ThisYearStart
ISOWeekNum = (AnyDate -
PreviousYearStart) \ 7
+ 1
YearNum = Year(AnyDate) - 1
Case Else
ISOWeekNum = (AnyDate - ThisYearStart) \ 7 + 1
YearNum = Year(AnyDate)
End Select
If IsMissing(WhichFormat) Then Exit Function
If WhichFormat = 2 Then
ISOWeekNum = CInt(Format(Right(YearNum, 2), "00") & _
Format(ISOWeekNum, "00"))
End If
End Function
Calculating Age In VBA
The following function will compute a person's age, returning a string such
as "45 years 10 months 18 days".
Function Age(Date1 As Date, Date2 As Date) As String
Dim Y As Integer
Dim M As Integer
Dim D As Integer
Dim Temp1 As Date
Temp1 = DateSerial(Year(Date2), Month(Date1), Day(Date1))
Y = Year(Date2) - Year(Date1) + (Temp1 > Date2)
M = Month(Date2) - Month(Date1) - (12 * (Temp1 > Date2))
D = Day(Date2) - Day(Date1)
If D < 0 Then
M = M - 1
D = Day(DateSerial(Year(Date2),
Month(Date2) + 1, 0)) + D + 1
End If
Age = Y & " years " & M & " months " & D & " days"
End Function
Dates In The Find Method
Using the .Find method to search for dates can be a
bit tricky. Regardless of how the date is formatted to display (as long as it is a
date format of some sort), you must search for it in its "standard" format,
e.g., "7/18/1998" rather than "7/18/98" or "July 18, 1998".
For example,
Set FoundCell = Range("A1:A100").Find (what:="7/18/1998")
Alternatively, you can use the DateValue function to
convert any date format into an Excel serial date, and search the formulas of the range
(even though they are not formulas in the conventional sense) to find your date.
Set FoundCell = Range("A1:A100").Find _
(what:=DateValue("July 18, 1998") ,lookin:=xlFormulas)
|