You are here: Home / Date and Time

How to create a custom time format

December 4, 2008 By

Excel provides a vast number of common time formats. However if you do need a format that is not included in the built-in list then you can create your own custom format as follows:

  1. Right-click on the cell where the date is.
  2. Select Format Cells.
  3. When the dialogue box opens select the Number tab.
  4. In the Category list to the left select Time.
  5. In the Type list to the right select the time format closest to what you want. A sample format will show above.
  6. In the Category list to the left select Custom. A list of custom time formats will show to the right, and the format you selected will show in the Edit Box above.
  7. Modify the format in the Edit Box and watch the sample.
  8. When you are satisfied click OK.
  9. The new format will be applied to the cell and will also be added to the list of custom formats.

Applies to: Excel 2003

Filed Under: Date and Time, Formatting Tagged With: , ,

How to insert update date or timestamp in a cell

December 1, 2008 By

A few months ago a user asked this question in Excel Help Forum:

If you have two columns, is it possible to have column B put in the date that data was added to column A?

I have replied to the question in the forum at that time, and I have noticed since then that other users are asking the same question. For that reason I thought it might be a good idea to elaborate on the subject here.

To insert an update date or a timestamp in a cell, you need to use a worksheet event called “Worksheet Change”. Just follow these steps:

1. Switch to visual basic editor by clicking Tools –> Macro –> visual basic editor.

2. Click on the name of your sheet in project explorer at your left.

Timestamp1

3. On the right pane select “Worksheet” from the left drop down and select “Change” from the right drop down.

4. Now you are in the Worksheet_Change event.

5. Copy this code between “Private Sub Worksheet_Change” and “End Sub”.

Col = Left(Target.Address, 2)
If Col = “$A” Then Target.Offset(0, 1) = Now

Timestamp2

Now whenever you change a cell in column “A” the next cell in column “B” will be updated with date and time.

You can change the code to suit your own needs, but the main idea is that you have to use the “Worksheet Change” event for this purpose.

Filed Under: Date and Time Tagged With: , , ,

Calculate the number of months between two dates

November 16, 2008 By

If you want the number of months occurring regardless of the day of the month then continue reading. If you want to calculate ‘whole month intervals’ by considering the day of the month, then check this post.

To calculate the number of months between two dates, there are two possibilities:

1. Both dates occur in the same year.
2. Dates occur in two different years.

Case 1 – both dates occur in the same year

Use the MONTH function to extract the month from each date, then subtract the two months from each other.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/09/2007 in cell A1 and the date 31/12/2007 in cell B1 (both cells formatted as date). To calculate the number of months between these two dates you use the following formula:

=MONTH(B1) – MONTH(A1)

This should give a result of 3.

Note that the cell where you write the formula must be formatted as number and not date.

Case 2 – dates occur in two different years

Use the the YEAR function and the MONTH function.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/09/2007 in cell A1 and the date 31/03/2008 in cell B1 (both cells formatted as date). To calculate the number of months between these two dates you use the following formula:

=(YEAR(B1)-YEAR(A1))*12+MONTH(B1)-MONTH(A1)

This should give a result of 6.

Again the cell where you write the formula must be formatted as number and not date.

Filed Under: Date and Time, Formulas Tagged With: , ,

How to calculate the difference between two dates

November 14, 2008 By

To calculate the difference or the number of days between two dates, you simply subtract one date from the other.

Example (using format:dd/mm/yyyy):

Suppose you have the date 25/11/2007 in cell A1 and the date 15/01/2008 in cell B1 (both cells formatted as date). To calculate the difference between these two dates you use the following formula:

=B1 – A1

This should give a result of 51 ( 5 days in Nov + 31 in Dec + 15 in Jan).

Note that the cell where you write the formula must be formatted as number and not date.

Filed Under: Date and Time, Formulas Tagged With: ,

Macro based yearly calendar template

October 29, 2008 By

I have added to the downloads page a yearly calendar template. This is a dynamic template that will allow you to generate a calendar for any year between 2001 and 2016. There are two combo boxes for the user to select the year and the start date of the week. Each selection will trigger a macro that will generate the calendar. Here are a few notes to consider:

To Enable Macros:

Select : Tools —> Macro —> Security —> Security Level —> Medium

Names of days and months

I have put the names of days and months as tables in the “Settings” worksheet. This will allow you to translate them in your own language if necessary. You have to unprotect the sheet first before you can do that.

Protection

1. All sheets are protected to avoid accidental change of data and formulas. If you want to make some changes like formatting for example, then you can unprotect the sheet. Make sure however that you don’t mess up the formulas.
2. Columns X and Y are hidden but not protected. These columns receive data from the two combo boxes for the year and the start date.

Printing

You can print the calendar just like any other Excel worksheet. The print area is set in a way that it will only print the year and the 12 boxes for the months, i.e. without the combo boxes.

VBA code

If you want to see the code behind all this, then switch to visual basic editor. The main routines are in module2, but some of the code is within different events of the workbook and the combo boxes.

Your feedback is appreciated.

Applies to: Excel 2003

Filed Under: Date and Time, General, Visual Basic Tagged With:

How to get the week number of a given date

October 22, 2008 By

To get the week number of a given date, i.e. to find out where the week falls numerically within a year you use the WEEKNUM function.

Syntax

WEEKNUM(serial_num,return_type)

serial_num : is a valid date. E.g. August 15, 2008 or 12/10/2008.

Return_type : is a number that determines on which day the week begins. (1 = Sunday, 2 = Monday). The default is 1.

Example:

If cell A1 = 10/19/2008 , which is a Sunday, then:

=WEEKNUM(A1,1) will return a week number of 43 (week begins in Sunday).

=WEEKNUM(A1,2) will return a week number of 42 (week begins in Monday).

Note:

WEEKNUM function is part of the Analysis ToolPak add-in. If it is not available in your list of functions then you have to install the Analysis ToolPak. Check the following link for more help:

install and load the Analysis ToolPak add-in

Important note (from Microsoft Excel Help)

The WEEKNUM function considers the week containing January 1 to be the first week of the year. However, there is a European standard that defines the first week as the one with the majority of days (four or more) falling in the new year. This means that for years in which there are three days or less in the first week of January, the WEEKNUM function returns week numbers that are incorrect according to the European standard.

Applies to Excel 2003

Filed Under: Date and Time, Formulas, Functions Tagged With: ,

How to count cells that contain numbers or dates

August 24, 2008 By

To count cells, within a given range or list of arguments, that contains only numbers you use the COUNT function.

  • Dates and text that represent numbers are considered numbers and will therefore be counted.
  • Logical values, text, or error values are not counted

Syntax

COUNT(value1,value2,…)

Examples:

COUNT(A1:A20) :counts numbers in a range of cells.
COUNT(A1:A20, 7) :counts numbers in a range of cells plus the second argument (7) which is counted as 1.

In the simple example depicted by the image below, cell B7 contains the function: =COUNT(B1:B6). The result is 4. as you can see the cells that contain the numbers 1, 2 and 17 and the cell that contains the date are counted. The the cells that contain the text “ABC” and “XYZ” are ignored.

Applies to: Excel 2003

Filed Under: Data, Date and Time, Functions Tagged With: , ,

How to format a date with month in upper case

July 31, 2008 By

If you want to format a date with month in upper case, then hear is how you do it:

1. The general format for getting the month in three character is this:

=TEXT(cell_reference,”mmm”)

2. To get upper case you enclose the TEXT function within the UPPER function as follows:

=UPPER(TEXT(cell_reference,”mmm”))

So if we have a date in cell A1 for example then our functions will be as follows:

=UPPER(TEXT(A1,”mmm”))

If you want to return the full date then you have to concatenate the day and the year with the month as follows:

=DAY(A1)&UPPER(TEXT(A1,”mmm”))&YEAR(A1)

The final result will be somthing like: 15AUG2008

Filed Under: Date and Time, Formatting, Formulas, Functions Tagged With: , , ,

Function to return the day of the week in text (Name of the day)

May 11, 2008 By

Excel has a built in function which returns the day of the week corresponding to a date. The day is given as an integer, ranging from 1 (Sunday) to 7 (Saturday), by default, or 1 (Monday) to 7 (Sunday) if you use a return type of 2. But there is no direct function to return the name of the day itself i.e. Monday, Tuesday…etc.The following code is an attempt to handle this case.


Function WeekdayText(dDate, iRtype)
‘ get the weekday no. from excel bilt-in Weekday function
iwday = WorksheetFunction.Weekday(dDate)
If iRtype = 2 Then
‘ Return the full name of the day
WeekdayText = WorksheetFunction.Text(iwday, “ddddd”)
Else
‘ Return the short name of the day
WeekdayText = WorksheetFunction.Text(iwday, “ddd”)
End If
End Function

The function accepts two arguments: Date and return type. If the return type is 1, the function will return an abbreviated name of the day i.e. Mon, Tue …. etc. If the return type is 2, the function will return the full name of the day i.e. Monday, Tuesday …. etc.

Use this code to create a user defined function and then you can use it like any other built-in function.

If you are not familiar with visual basic then you can achieve the same result by encapsulating the Weekday function within the Text function, in a cell, as follows:

=TEXT(WEEKDAY(date),”ddd”)

Related Links:

How to create a user-defined function

Applies to: Excel 2003

Filed Under: Date and Time, Formatting, Functions, Visual Basic Tagged With: , , , ,

Function to decide whether a year is a leap year or not

April 13, 2008 By

The following Visual Basic code is for a function to determine whether a certain year is leap year or not. It will receive the year as an argument and return TRUE for a leap year and FALSE for others.Copy this code and paste it in a new visual basic module.


Function IsLeap(iYear)
If (iYear Mod 400) = 0 Then
IsLeap = True
ElseIf (iYear Mod 100) = 0 Then IsLeap = False
ElseIf (iYear Mod 4) = 0 Then IsLeap = True
Else: IsLeap = False
End If
End Function

Related Links:

Blog Post: How to create a user-defined function
External: Wikipedia article on Leap_year

Applies to: Excel 2003

Filed Under: Date and Time, Functions, Visual Basic Tagged With: , ,
« Older Posts
Newer Posts »