VBA date function, day in the year?

[Steven Marzuola]

1. Is there a function, or a formatting string for the DATE
function, that will give the number of the current day counting from
the beginning of the year? For example, Mar 24, 2005 is day 83 (31
days in January + 28 in February + 24).

2. Same, number of days in the current month?

3. Same, number of days in current year (accounting for leap years)?

I'm using Office 2000. Thanks,
Steven

 

[Jay Freedman]

1. Is there a function, or a formatting string for the DATE
function, that will give the number of the current day counting from
the beginning of the year? For example, Mar 24, 2005 is day 83 (31
days in January + 28 in February + 24).

2. Same, number of days in the current month?

3. Same, number of days in current year (accounting for leap years)?

I'm using Office 2000. Thanks,
Steven

Hi Steven,

Most of this sort of stuff can be done with the Format function and the
Month and Year functions. For a list of the special formatting characters
used with dates, see the VBA help topic "User-Defined Date/Time Formats
(Format Function)". The type conversion functions CDate and CString are also
helpful.

The Date data type is aware of leap years and automatically takes account of
them when you do date arithmetic.

The Format function includes an option for the day-of-year number. For the
number of days in the year, you can just take the day-of-year number of
December 31. The number of days in the month is a little harder -- you get
the first day of the following month, subtract 1 to get the preceding day
(which may be the 28th, 29th, 30th, or 31st), and take the day part of that
date.

The macro below goes through the process in tedious detail as a teaching
aid; in production code you can take some shortcuts.

Sub DisplayDates()
Dim dtDate As Date, dtEOM As Date, dtEOY As Date
Dim dtFirstOfNextMonth As Date
Dim strNextMonth As String, strThisYear As String
Dim strFirstNextMonth As String
Dim strIn As String, strOut As String

strIn = InputBox("Enter a date:")
If Not IsDate(strIn) Then Exit Sub

' convert String to a Date
dtDate = CDate(strIn)

' get the number of the next month
strNextMonth = CStr((Month(dtDate) + 1) Mod 12)
' get the number of the year
strThisYear = CStr(Year(dtDate))

' construct string for first day of the next month
strFirstNextMonth = strNextMonth & "/1/" & strThisYear
' convert it to a Date
dtFirstOfNextMonth = CDate(strFirstNextMonth)
' compute last day of this month
' as the day before dtFirstOfNextMonth
dtEOM = dtFirstOfNextMonth - 1

' construct string for last day of year
' and convert it to a Date
dtEOY = CDate(Format(dtDate, "12/31/yyyy"))

' construct the output string
strOut = Format(dtDate, "MM/dd/yyyy") & _
" day of year = " & Format(dtDate, "y")
strOut = strOut & vbCr & "days in " & Format(dtDate, "mmmm") _
& " = " & Format(dtEOM, "d")
strOut = strOut & vbCr & "days in " & Format(dtDate, "yyyy") _
& " = " & Format(dtEOY, "y")

MsgBox strOut
End Sub

 

[Jezebel]

1. Is there a function, or a formatting string for the DATE function, that
will give the number of the current day counting from the beginning of the
year? For example, Mar 24, 2005 is day 83 (31 days in January + 28 in
February + 24).

2. Same, number of days in the current month?

3. Same, number of days in current year (accounting for leap years)?

I'm using Office 2000. Thanks,
Steven

DayOfYear = CLng(Format$(Now, "y"))

DaysInYear = CLng(Format$(DateSerial(Year(Now),12,31), "y"))

DaysInMonth = Choose(Month(Now), 31,28,31,30,30,31,30,31,30,31,30,31) - _
(Month(Now) = 2 and
Month(DateSerial(Year(Now),2,29) = 2)