Excel Date Functions

Excel DatePart and other Date Functions

DatePart Excel Function is used to extract a speic frequency of a date. For example, let’s assume we want to extract the month from a date.

The Basics:

DatePart(
interval,
date,firstdayofweek,
firstweekofyear
)

Interval = The interval you want to extract. For example if you want to note the week you would use the ‘ww’ value. If you working with days you would use the ‘d’ attribute.

Date = The date that you want to work with

Firstdayofweek = What day is considered the first day of the week. The value is optional and the default value is set at Sunday

Firstweekofyear = You can define the first week based on what you require. For example the default value for the first week is the week in which Jan 1 appears.

Now let’s look at an example. Let’s assume we have a date, and we want to extract the year, week and weekday values.

Code

Dim Date1 As Date’Variable to hold the date value

Date1 = ActiveSheet.Cells(2, 1).Value’Assign the value of our first date to the Date1 variable

‘Now let’s extract the year

ActiveSheet.Cells(8, 1).Value = DatePart(“yyyy”, Date1)

 

‘Now let’s extract the week

ActiveSheet.Cells(10, 1).Value = DatePart(“ww”, Date1)

span>

‘Now let’s extract the weekday

ActiveSheet.Cells(12, 1).Value = DatePart(“w”, Date1)

Output

Note:

The Weekday value is represented in Excel by an integer (refer to cell C2). Based on what you defined as your first day of the week the function will count forward after that. For example, the default value for the function is Sunday = 1. The 1/1/2010 was Friday so that is why it is represented by the value of 6.

Other useful date functions

The following functions perform similar functions as the Excel DatePart function namely:

·
<!–[endif]–>
Day

·
<!–[endif]–>
Hour

·
<!–[endif]–>
Minute

·
<!–[endif]–>
Month

·
<!–[endif]–>
Weekday (Return a numerical value of the weekday based on the first day of the week. You have the option to define the first day of the week as well.

·
<!–[endif]–>
Weekdayname (Return the actual weekdayname such as Monday, Tuesday etc.). You have the option to define the first day of the week as well.

·
<!–[endif]–>
Year

Example

Now let’s assume that we want to extract specific values from a date.

Code

 Dim Date1 As Date’Variable to hold the date value

Date1 = ActiveSheet.Cells(2, 1).Value’Assign the value of our first date to the Date1 variable

‘Now let’s extract the day

 ActiveSheet.Cells(2, 2).Value = Day(Date1)

‘Now let’s extract the hour

 ActiveSheet.Cells(4, 2).Value = Hour(Date1)

‘Now let’s extract the minute

 ActiveSheet.Cells(6, 2).Value = Minute(Date1)

‘Now let’s extract the month

 ActiveSheet.Cells(8, 2).Value = Month(Date1)

‘Now let’s extract the weekday

 ActiveSheet.Cells(10, 2).Value = Weekday(Date1)

‘Now let’s extract the weekdayname

ActiveSheet.Cells(12, 2).Value = WeekdayName(1) ‘Note that the weekdayname returns the name of the day based on a numerical value.

‘So considering that the default first day of the week is Sunday the output for

‘this function will be Sunday.

‘Now let’s extract the year

ActiveSheet.Cells(14, 2).Value = Year(Date1)

Output