You are here: Home / Archives for Date

How to insert current date in worksheet footer

January 27, 2009 By admin

To insert the current date in worksheet footer, follow these steps:

1. In the File menu click Page Setup. The Page Setup window will open.
2. Select the Header/Footer tab.
3. Click the Custom Footer button. The Footer window will open.

footicons

As you can notice the footer is divided into three sections; left, center and right. Above those sections there is a number of icons for page number, pages, date, time and more other icons.

To insert the current date:

1. Click on the section where you want the date to be inserted.
2. Click the Date icon footerdate.
3. The following code will be inserted in the selected footer section: “&[Date]“.
4. Click OK and you will see the current date in the preview window.
5. Click OK again to finish the dialog.

Notes:

1. You can also go to Header/Footer by clicking  Header and Footer in the View menu.
2. Use File –> Print Preview to see how your footer will look like in the printer.
3. You can use a similar procedure to insert the date in the header if you want to.

Applies to: Excel 2003

Filed Under: Customization, Date and Time, Formatting, Printing Tagged With: Customization, Date, Excel, Footer, Formatting, Header, Printing

Calculate the difference between two times

January 26, 2009 By admin

The way you calculate the difference between two times depends on the way you want to present your results:

1. Present the result in standard time format (hh:mm:ss).

Simply subtract the two times from each other and format the result cell as time.

Examples:

A1 = 9:15:00
B1 = 14:07:20
Difference = B1-A1 = 4:52:20 (4 hours, 52 minutes and 20 seconds).

or if you are using a 12 hour clock then the same example will be like this:

A1 = 9:15:00 AM
B1 = 02:07:20 PM
Difference = B1-A1 = 4:52:20 (4 hours, 52 minutes and 20 seconds).

If you want the difference in hours and minutes or even in hours only, then just customize the cell format for that purpose.

Notes:

When you use this option then:

1. Your result should always be positive. I.e. the time in B1 must be later than the time in A1.

2. Hours will not exceed 24 and minutes  or seconds will not exceed 60.

For more help on time formatting check this post:

How to create a custom time format

2. Present the result as a total of hours, minutes or seconds:

Example:

A3 = 1/25/2009  21:03

B3 = 1/26/2009  23:17

1. To get the total hours between the two times use the formula:

=INT((B3-A3)*24). The result is 26.

2. To get the total minutes between the two times use the formula:

=(B3-A3)*1440. The result is 1574.

3. To get the total seconds between the two times use the formula:

=(B3-A3)*86400. The result is 94462.

Notes:

1. In all three cases (B3-A3) will give the number of days. Then you multiply by the apprpriate constant to get either hours, minutes or seconds.

2. INT function is used in the first case to get whole number. If you are interested in the fraction then you can ignore the INT function.

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

How to extract day, month or year from a date

January 8, 2009 By admin

To extract day, month or year from a date, you use the corresponding Excel built in function for each one of these date parts.

  • Day: Day(mydate).
  • Month: Month(mydate).
  • Year: Year(mydate).

Examples:

Suppose you have in cell A1 (formatted as date), the date:15/11/2009 (ddmmyyyy), Then:

  • Day(A1) will result in 15
  • Month(A1) will result in 11
  • Year(A1) will result in 2009

Applies to: Excel 2003

Filed Under: Date and Time, Functions Tagged With: Date, Day, Month, Year

Calculate the number of months between two dates – knowledgebase article

December 27, 2008 By admin

This step-by-step article shows you how to calculate the number of months between any two dates.

To calculate the number of months between any two dates, use one of the following methods. Note that both methods use the following information:

• EDate = Earlier Date
• LDate = Later Date

NOTE: If you type LDate or EDate directly into this formula instead of into their cell references, you must surround it by quotation marks (for example, “9/7/00″).

Round Up

This method does not use the day of the month in its calculations. For example, given a start date of 10/31/00 and an end date of 11/2/00, one month is returned even though only two days elapsed.

For this method, use the following formula:

=(YEAR(LDate)-YEAR(EDate))*12+MONTH(LDate)-MONTH(EDate)

as shown in the following example:

1. Type 10/31/99 in cell A1.
2. Type the following formula in cell A2:

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

The number that is returned equals the number of months from 10/31/99 to today’s date, rounded up to the next whole number.

Round Down

This method uses the number days in its calculations and rounds down to the nearest number of whole months. For example, given a start date of 10/30/00 and an end date of 11/30/00, one month is returned; however, an end date of 11/29/00 returns a zero.

For this method, use the following formula

=IF(DAY(LDate)>=DAY(EDate),0,-1)+YEAR(LDate)-YEAR(EDate))
*12+MONTH(LDate)-MONTH(EDate)

as shown in the following example:

1. Type 10/31/99 in cell A1.
2. Type the following formula in cell B1:

=IF(DAY(NOW())>=DAY(A1),0,-1)+(YEAR(NOW())-YEAR(A1)) *12+MONTH(NOW())-MONTH(A1)

NOTE: Ensure that cell B1 is formatted as General. The number returned equals the number of months from 10/31/99 to today’s date, rounded down to the nearest number of whole months.

Source: Microsoft Help and Support
Original article: http://support.microsoft.com/kb/214134

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

How to get the current date and time

December 13, 2008 By admin

If you want to get the current date and time in a cell then you can do one of the following:

1. To get the current date only use the TODAY() function.

Syntax

=TODAY()

2. To get the current date and time use the NOW() function.

Syntax

=NOW()

3. To get the current time only use the NOW() function, and then format the cell to display time only.

Note:

The TODAY() and NOW() functions will update the date and time whenever the worksheet is calculated. I.e. their values are variable.

To insert static date and/or time in a cell:

1. For current date: Select a cell, press CTRL then enter “;” (semicolon character).

2. For current time: Select a cell, press CTRL+SHIFT then enter “;”.

3. For current date and time: Select a cell, press CTRL then enter “;” to get the date. Enter SPACE, press CTRL+SHIFT then enter “;” to get the time.

You can also use Copy/Paste special. For example to To insert static date and time in a cell:

1. Enter “=NOW()” in a cell and hit enter. You will get the current date and time

2. Right click on the cell and select Copy.

3. Right click on the same cell and select Paste Special, then select Paste Values.

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

How to insert update date or timestamp in a cell

December 1, 2008 By admin

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: Date, Date and Time, Time, Timestamp

Calculate the number of months between two dates

November 16, 2008 By admin

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: Date, Dates, Month

How to calculate the difference between two dates

November 14, 2008 By admin

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: Date, Dates

How to format a date with month in upper case

July 31, 2008 By admin

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: Date, Format Date, Month, Uppercase

How to create a custom date format

March 29, 2008 By admin

Excel provides a vast number of common date 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 Date.
  5. In the Type list to the right select the 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 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: Date, Excel, Excel help, Formatting