These pages are no longer updated and are only available for archive purposes.
Click here to visit the pages with updated information.
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
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.
||The current date and time
||The date and time 12 hours later than now (24 hours/day)
||The date and time 10 minutes later than now (1440
||The date and time 30 seconds later than now (86400
If you enter two hours in two different cells, you can perform a calculation with the
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
Ole P. from Norway wrote (2006-08-31 23:03:46 CET):
You can set the numberformat using VBA like this:
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):
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?