Excel Time Formulas

Microsoft Excel Spreadsheets

Microsoft Excel Tips

Excel
Spreadsheets Tips

Excel
VBA (macros) Tips

My
Excel Tools

Contacts

Excel time formulas allow you to substract times and dates to obtain days and hours like you would do with regular numbers but....

The format of the cells receiving the result must be Format/Cells/Number/Number when working with dates.
 The format of the cells receiving the result must be Format/Cells/Number/Time/37:30:55 when working with times.

When you want to multiply hours and minutes by a rate to obtain a salary you must use the following formula:
=(DAY(A1)*24*B1)+(HOUR(A1)*B1)+(MINUTE(A1)/60*B1)
when the time is in A1 and the rate is in B1
the format of the cells receiving the results must be "currency"

See many examples of Excel time formulas including a time management application with no macros.

  

Excel Time Formulas

You want to detemine the age of a person. If in cell "A3" you enter the date of birth, and in cell "B3" today's date, the following formula in "C3" would give you a good approximation of the age (plus or minus a few days):
=INT((B3-A3)/365) & " years and " & TRUNC((MOD((B3-A3);365))/30) & " months"

Dates and times are serial numbers not numbers so I suggest that you always use the "DATE" function when dealing with them.

With the DATE function, the arguments are in the following order (year,month,day) whatever the date format specified in your regional parameters.

DATE, DAY, MONTH, YEAR

With a date in cell A1 ;
the formula to add a day :
=DATE(YEAR(A1),MONTH(A1),DAY(A1)+1)
the formula to add a week :
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)+7)
the formula to add a month :
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1))
the formula to add a year :
=DATE(YEAR(A1)+1,MONTH(A1),DAY(A1))
the last day of the month preceding the date in A1
=DATE(YEAR(A1),MONTH(A1),DAY(A1)-DAY(A1))
the first day of the month following the date in A1
=DATE(YEAR(A1),MONTH(A1)+1,DAY(A1)-DAY(A1)+1)

Hours and Minutes

To sum hours over a total of 24, you must modify the format of the result cell. "Format"/"Cell"/"Personalized"/"[h]:mm"

To transform minutes in hours and minutes (125 becoming 2:05), use this simple formula (the format of the destination cell must be hh:mm):
=A1/1440

 

Support, Consulting and Training on
Microsoft Excel and VBA (macros)

1-800-501-6760 (Peter)

 

excel-vba.com

The senior super user helping other users on line since 1995

Microsoft
Excel Tips

Microsoft Excel
VBA (macros) Tips

Links to other
Excel-VBA Resources