ERLANDSEN DATA CONSULTING Excel & VBA Tips

### 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 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