Calculate with time

The function =NOW() can be used to return the current date and/or current time, depending on which number format you use in the cell that contains the function. The function returns a serial number for the current date. The integer part is the serial number for the current date (see Dates for more details).
The decimal part is the time for the current day, and is calculated by dividing the hour by 24 hours. The hour 12:30 returns the decimal number 12.5/24=0.5208333.

Examples:

Formula :	Returns :
`=NOW()`	The current date and time
`=NOW()+12/24`	The date and time 12 hours later than now (24 hours/day)
`=NOW()+10/1440`	The date and time 10 minutes later than now (1440 minutes/day)
`=NOW()+30/86400`	The date and time 30 seconds later than now (86400 seconds/day)

If you enter two hours in two different cells, you can perform a calculation with the two hours.
If cell A1 contains the hour 16:00 and cell B1 contains the function =TIME(HOUR(NOW()),MINUTE(NOW()),0), you can enter the formula =A1-B1 in cell C1 to calculate the difference in time between the two hours. Cell C1 must be formatted to display hours, not numbers.

A colon (:) is used as a separator sign between hours, minutes and seconds when you enter hours in a cell.

To convert an Excel timevalue to an ordinary decimal value you multiply the timevalue with 24. If cell A1 contains a weekly sum of working hours, you can calculate the amount of money you will get like this (assumes a rate of 250 dollars or pounds or kroner or whatever/hour): =A1*250*24

If you want to add timevalues greater than 24 hours you will have to use the custom number format [hh]:mm on the cells that you want to display timevalues greater than 24 hours.

Document last updated 2000-02-04 12:45:22 Printerfriendly version

User comments:

Ole P. from Norway wrote (2006-08-31 23:03:46 CET):

Re: Seconds
You can set the numberformat using VBA like this:
ActiveCell.NumberFormat="hh:mm:ss AM/PM"

You can also use the same numberformat in Excels Format Cells dialog and create a custom number format.

fred from usa wrote (2006-08-31 20:36:43 CET):

Seconds
I want to display the time in a cell that includes the seconds, for example 12:45.58 PM. How do i code this in VBA?