| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 70866 | 2006-07-19 03:45:00 | excel query | jonp (7517) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 471943 | 2006-07-19 03:45:00 | 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 ? |
jonp (7517) | ||
| 471944 | 2006-07-19 03:53: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 |
SolMiester (139) | ||
| 471945 | 2006-07-19 04:22: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. |
jonp (7517) | ||
| 471946 | 2006-07-19 04:56:00 | 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 |
SolMiester (139) | ||
| 471947 | 2006-07-19 05:06:00 | 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) |
jonp (7517) | ||
| 471948 | 2006-07-19 05:14:00 | seems to have problems going over certain amount of hours.....interesting | SolMiester (139) | ||
| 471949 | 2006-07-19 05:19:00 | Haha....LOL, yes I see now.....will play a bit | SolMiester (139) | ||
| 471950 | 2006-07-19 05:27:00 | Got it...Change C1 to =HOUR(B1-A1) as general format |
SolMiester (139) | ||
| 471951 | 2006-07-19 05:34:00 | 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). |
Graham L (2) | ||
| 471952 | 2006-07-19 07:25:00 | 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 |
SolMiester (139) | ||
| 1 2 | |||||