DateValue
This function converts a date into a value. For example, the following will return the value 37686, which is the date 6-Mar-2003:
Msgbox CDbl(DateValue("06-Mar-2003"))
You need to use CDbl (convert to double) in this code or the message box will display the date per the format in the Windows Control Panel rather than as an actual number.
Day
This will return an integer between 1 and 31, representing the day of the month for the date expression given, as seen here:
Day (dateexpression)
Dateexpression can be a date string or it can be a numeric expression representing a date. Both of the following return the value 6 for the sixth day of March because they represent the same date:
Msgbox Day(37686)
Msgbox Day("6-Mar-2003")
Hour
Hour returns an integer between 0 and 23, representing the hour of the day for the date expression:
Hour(dateexpression)
An example of a dateexpression could be "31-Dec-2002 12:00" or could be a time without the date, such as "09:00":
MsgBox Hour("17:50")
This will return a value of 17 for the 17th hour.
The following will return a value of 16 because 4:30 in the afternoon is the 16th hour:
MsgBox Hour("6-Mar-2003 4:30pm")
The following will return the value of 11; 11 divided by 24 is equal to .458333, which is the time value for 11:00 a.m.:
MsgBox Hour(11 / 24)
Month
Month returns an integer between 1 and 12, based on the date expression:
Month (dateexpression)
An example of a dateexpression could be "31-Dec-2002 12:00" or could be a time without the date, such as "09:00."
The following will both return the value of 3 because both date expressions represent 6-Mar-2003:
Msgbox Month(37686)
Msgbox Month("6-Mar-2003")
Second
The Second function returns an integer between 0 and 59 based on the timeexpression representing the seconds of a minute:
Second(timeexpression)
An example of a timeexpression could be "31-Dec-2002 12:00" or could be a time without the date, such as "09:00."
The following will return the value 48:
Msgbox Second("4:35:48pm")
Minute
The Minute function returns an integer from a time expression representing the actual minute of that time.
Msgbox Minute(11.27 / 24)
This will return the value 16, since 11.27 is 11:16:12 a.m. This may look confusing because
we are dealing with decimal parts of an hour. The expression 11.27 is a decimal of an hour,
so .27 is just over a quarter of an hour.
Following are two examples of the Minute function:
Msgbox Minute("4:35pm")
Msgbox Minute(11.25 / 24)
Year
The Year function returns an integer from a date expression representing the actual year value of that date:
Msgbox Year(37686)
Msgbox Year("6-Mar-2003")
Weekday
The Weekday function returns an integer between 1 (Sunday) and 7 (Saturday) that represents the day of the week for a date expression:
Weekday (dateexpression)
MsgBox WeekDay("6-Mar-2003")
This will return the value 5, which is Thursday.
This function can be useful if you want a date to always default to a particular day of the
week. For example, if you always want a date to show the week ending Friday for the current
week, you could use the following formula:
MsgBox Now - WeekDay(Now) + 6
The Weekday function starts from Sunday, so it reduces Now back to the last Sunday and then adds 6 to get to Friday. You can also use it to calculate the number of working days between two dates:
For n = DateValue("1-Jan-03") To DateValue("18-Jan-03")
If Weekday(n) <> 1 Or Weekday(n) <> 7 Then
WorkDay = WorkDay + 1
End If
Next n
MsgBox WorkDay
WorkDay will return the value of 13, which is the number of working days between the two dates.