Microsoft Office Forums How to get cells to calculate time?
 Register FAQ Search Today's Posts Mark Forums Read

#1
08-22-2006, 08:51 AM
 jrasche2003@yahoo.com Novice Join Date: Aug 2006 Posts: 2
How to get cells to calculate time?

I am having a difficult time in figuring out what formula to use.

I use Excel for a timesheet format
In cell A1----7:30 a.m.

In cell A2----4:00 p.m.
I want A3 to autosum the total amount of these hours, which is 8...

What formula do I use?

Thanks!!!!
:D
#2
09-29-2006, 08:43 PM
 Ziggy1 Novice Join Date: Sep 2006 Location: Ont, Canada Posts: 8

this will work as long as your hours stay with in one day

Format your Time Cells as Time and the cell with this formula as an Number format

=(EndTime - StartTime)/24
#3
02-09-2007, 07:10 AM
 jhweaver Novice Join Date: Jan 2007 Posts: 6
Time in Excel

When calculating time, use normal formulas i.e. =A1+A2, etc

However, if the resulting sum exceeds 24 hours, you get some strange results. The way to correct this is to format the cell using CUSTOM formatting.

Set the cells holding the time(s) to normal hh:mm format, but in the cell where you want to calculate the totals set the cell format using custom to [h]:mm. This will correct the 24 hour problem.

Try this;

Put 7:45 into 4 consecutive cells (all formatted as hh:mm) and SUM the values. If the target cell (SUM) is formatted as hh:mm the answer will be 7:00

That's because the 4x7:45=31 hours
Because that is greater than 24 (hours), the difference is 7 hours (the answer).

If you change the target cells format to [h]:mm, it will now correctly answer 31:00 (31 hours)

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post dirtleg Excel 1 09-16-2008 01:52 PM kevin_ms Excel 4 09-14-2008 10:08 PM cristi22 Excel 0 10-23-2006 08:28 AM dipdog Excel 0 08-17-2006 08:37 AM AdamNT Excel 1 08-11-2006 11:51 PM

All times are GMT -7. The time now is 05:24 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top