View Full Version : excel query

I am trying to formualte a spreadsheet that will automatically work out the number of hours if i enter the start and finish times on a shift and then tally all the days for the week. The start and fininsh times i have specified the cells as time hh:mm and the total number is cell 1 - cell 2, however i cannot get the total to add correctly.

any ideas ?

SolMiester

19-07-2006, 03:53 PM

Hi

Cell A1

=TIME(9.0.0)

Cell B1

=TIME(11.30.0)

Cell C1

=B1-A1+1

Format Cell C1 in Time format xx.xx.xx

Answer 2.30.00

Hi

Cell A1

=TIME(9.0.0)

Cell B1

=TIME(11.30.0)

Cell C1

=B1-A1+1

Format Cell C1 in Time format xx.xx.xx

Answer 2.30.00

Thanks Solmeister. I have got that far, and when I do monday to friday as a sum of the column it does not appear to work.

SolMiester

19-07-2006, 04:56 PM

Jon, cant see why?, separate row as above for each day, on 6th row add column C1-C5, works fine here.

Get the 1st row working, copy down for Rows Tues-Fri, & the 6th row sum should be easy, ensure you have format of cell correct to understand answer.

PM me your e-mail if u want spreadsheet

If I have start time as 09:00 and finish time as 19:00 that equals 10 hours (in cell C1). if that is repeated 5 times and then the column added it gives me an answer of 02:00 whereas it should be 50 hours. I have the cell that tallys the totals formatted as hh:mm (the same as all the other cells).

ie cell A1 09:00

cell b1 19:00

cell c1 (b1-a1) 10:00

repeated on rows 2 to 5 .

cell c6 = sum(c1:c5)

SolMiester

19-07-2006, 05:14 PM

seems to have problems going over certain amount of hours.....interesting

SolMiester

19-07-2006, 05:19 PM

Haha....LOL, yes I see now.....will play a bit

SolMiester

19-07-2006, 05:27 PM

Got it...Change C1 to

=HOUR(B1-A1) as general format

Graham L

19-07-2006, 05:34 PM

The 2:00 is correct, in its own devious way. 50 modulo 24 is 2 .

Anyway, wouldn't most employers in the new reformed labour market like to calculate pay like that. (Except for the managers, of course).

SolMiester

19-07-2006, 07:25 PM

Yes...of course Graham!!...my bad.....the format was after all HH.MM.SS. It needed a DD day in front.

50 Hour - 48 hours ( 2 days .DD=02 ) = 02 Hours...02.00.00..hehe. Logical i guess if you think about it....hmmmm :horrified

Parry

20-07-2006, 05:37 AM

Hi, Im a bit late reading this thread but the way Excel cells are formatted can significantly change the way the results are displayed for dates/times.

As an example, if you type the 9:00 in cells A1:A5 and 15:00 in cells B1:B5 then in column C have the formula =B1-A1 copied down you will see that the result is 6:00 for these 5 results. So far so good. You will see the format by selecting Format|Cells is H:MM.

Now sum column C by =SUM(C1:C5) in C6 and you will see the answer is .... 6:00. Weird you may rightly say, the answer should be 30:00. To understand why this is displaying 6:00 in column D enter the formula =C1 in D1 then drag formula down to D6. Now in D1 to D6 select Format|Cells and select the Number category option with 2 decimal places. You will see D1 to D5 is showing 0.25 and D6 1.25. This is how Excel stores dates/times - as a number. The time of 6 hours is a 1/4 of a day hence 0.25. The integer (ie 0) is 0 because you havent specified a date but this is irrelevant to the problem.

Because C6 is formatted as H:MM then only the hour and minutes are displayed, not the days hence this leaves only the .25 bit rather than 1.25 so you will see only 6:00 displayed as the sum of C1:C5.

To avoid this issue simply change the format of the cell that contains the sum formula to this... [H]:MM. This will force the format to sum as hours and minutes and will display 30:00 as the result.

If you would prefer to display in words you can use a format like this...

d "days," h "hours and " mm "minutes"

Hope this gives you a bit of insight into how Excel stores and displays dates and times.

regards,

Graham

Powered by vBulletin® Version 4.2.1 Copyright © 2014 vBulletin Solutions, Inc. All rights reserved.