Convert time in decimals to a standard time

If you have a time in a decimal format, i.e. an integer and a fraction, e.g. 2.5 hours (2 and a half hours) and you want to convert to a standard time format 02:30 (2 hours and 30 minutes), then you can use a formula based on the TIME function with the following syntax:

TIME(hours,minutes,seconds)

Example:

We have a decimal time 2.5 in cell A2. we can use the following formula to convert it to a standard time:

=TIME(INT(A2);(A2-INT(A2))*60;0)

  • The first parameter in this formula,  hours is equal to the integer part of the decimal value: INT(A2).
  • The second parameter, minutes is equal to the fraction of the decimal value multiplied by 60:  (A2-INT(A2))*60.
  • The last parameter seconds, is set to 0 in our example.

Below are different examples based on this formula:

dtime1

Applies to Excel 2003

Add hours or minutes to a standard time

If you have a standard time, current time for example, and you want to add to it hours, minutes or seconds to get to a new time, then what you need to do is to convert the hours or minutes to a standard time format using the TIME function. After that you can add the two values together using the “+” sign.

A generic formula will look like this:

New time = current time + Time(hours,minutes,seconds)

In the examples below we have a standard time (07:15) in column A.

1. In row 2 we are adding 3 hours (B2) to 07:15 (A2). The formula in D2 will be:  =A2+TIME(B2;C2;0). The new time is 10:15.

2. In row 3 we are adding 4 hours (B3) and 15 minutes (C3) to 07:15 (A3). The formula in D3 will be:  =A3+TIME(B3;C3;0). The new time is 11:30.

3. In row 4 we are adding 20 minutes (C4) to 07:15 (A4). The formula in D4 will be:  =A4+TIME(B4;C4;0). The new time is 07:35.

4. In row 5 we are adding 12 hours (B5) to 07:15 (A5). The formula in D5 will be:  =A5+TIME(B5;C5;0). The new time is 19:15.

5. The last example in row six is slightly different. The result of the addition is more than 24 hours. If we use a formula similar to the ones shown above the result will be 01:15 while the actual value is 25 hours and 15 minutes. I.e. anything more than 24 hours will be lost. To avoid this problem we modify the formula in cell D6 slightly to look like this:

=(A6+TIME(B6;0;0))*24

We will also change the format of cell D6 to number instead of time. The result of this formula is 25.25 hours(.25 to the right of the decimal point is a decimal value and not minutes).

Notes:

1. Cells that contain time (Columns A and D should be formatted as TIME, cells that contain Hours or minutes (columns B an C) should be formatted as NUMBER).

2. I did not include seconds in these examples. If you want to add seconds then you can specify it as the third parameter of the TIME function.

3. The examples use a 24 hour clock. You can use 12 hour clock with AM/PM without problems.

addtime4

Applies to Excel 2003

How to add or sum times

If you have a number of time values you want to add together, e.g. total hours worked by an employee in the week, then you can list those hours in a standard time format (hh:mm or hh:mm:ss) and sum them together.

Example 1 : total hours less than 24.

Cells B1, B2, B3 and B4 contains the times.

Cell B5 (total) contains the formula:

=SUM(B1:B4)

The result of this sum is 23:05 (23 hours and 5 minutes).

Please note that cells B1to B5 are formatted as time (hh:mm).

addtime1

Example 2 – total hours greater than 24.

Cells B1, B2, B3 and B4 contains the times.

The total time in this example is 25 hours and 5 minutes. If we use the formula : =SUM(B1:B4), the result will be 01:05 (i.e 24 hours are gone).

To avoid this problem we use the following formula instead: =SUM(B1:B4)*24

We will also change the format of cell B5 to number instead of time. The result of this sum is 25.08 hours(.08 is a decimal value and not minutes).

addtime2

Applies to Excel 2003

Calculate the number of months between two dates – knowledgebase article

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

How to get the current date and time

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.

How to insert update date or timestamp in a cell

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.