Calculation involving Time

[SeethaSharma]

Hi,

I have a spreadsheet for Time and Attendance. In this spreadsheet, I have 2 columns -- Start and Stop time. Based on these, there is a 3rd column which calculates the no. of hours worked -- which, because the other 2 columns are defined as "time", also is a "time" column. Now, I need to add up the hours worked (which are all in time format) to find total number of hours worked in a week. If I use a simple SUM function, I get ridiculous results! How can I solve this problem? To give an example of my columns:

Start (time) Stop (time) Hours Worked (time)

07:30 15:30 8:00
07:30 16:30 9:00
08:30 20:00 11:30
Total ???

My problem will be solved (I think!) if I could get Excel to calculate the no. of hours worked (by subtracting start time from stop time) and storing that figure in a number column rather than a time column...but it won't do this either.

I would appreciate any help.

Seetha

 

[lupins46]

Format the total cell as:
[hh]:mm

 

[SeethaSharma]

Hi lupins46,

Thank you! That solved the problem. I really appreciate your help.

If you have the time, would you explain what is the difference between hh:mm and [hh]:mm -- why does that make such a big difference?

Seetha

 

[AnotherHiggins]

hh:mm displays only the hours and minutes associated with a date/time field. 2 days, 10 hours and 13 minutes will therefore display as 10:13 whereas formatting the same cell as dd hh:mm will give you 02 10:13.

Including the brackets tells Excel to display the entire date/time as the number of hours. So 2 days, 10 hours and 12 minutes will display 58:13.

Briefly, dates are stored in Excel as whole numbers and times are stored as decimals (figured by the percentage of 24 hours).

For more information on how Excel deals with dates and times, see the FAQ Why do Dates and Times seem to be so much trouble?

[tt]_____
[blue]-John[/blue][/tt]

Help us help you. Please read FAQ181-2886 before posting.

 

[SeethaSharma]

Hi John,

Thank you for the very clear explanation. I will look up the FAQ also.

Thanks again.

Seetha