| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 55882 | 2005-03-21 23:26:00 | Excel - Calcs with time | lloyd (1076) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 336586 | 2005-03-21 23:26:00 | I've been trying to get Excel (2002) to calculate a running balance of hours worked over or under a set amount of hours when working glide time. e.g. Start time in A2, stop time in B2, hours worked in C2, running balance in D2. If the balance starts at 0 and then an 8hr day is worked then the balance remains at 0, a 7hr day would drop the balance to -1, a subsequent 9hr day would lift it to 0 again and so on. I can get it to work fine for the number format only, likewise to calculate the number of hours worked each day in the time format by subtracting start time from stop time, but the problem occurs when trying to calculate the running balance and display a certain numbers of hours. Preferably I'd like the result to be displayed in hours and min but a decimal number would be OK. I would much appreciate any help here, cheers |
lloyd (1076) | ||
| 336587 | 2005-03-21 23:55:00 | Presumably in a column you are subtracting the elapsed time from 08:00 and getting ####### in cells where a negative should be. Format Cells these cells as NumberthenCustom and from the selection box highlight the entry _-* #,##0.000_-;-* #,##0.000_-;_-* "-"??_-;_-@_- and click OK. The negatives will now appear. To get your running balance, format a cell as hh:mm then enter the formula =SUM(startcell:endcell)*24 which will convert the value to hours and minutes. HTH |
rad_s4 (7401) | ||
| 336588 | 2005-03-22 00:41:00 | Whoops - slight error, if the running balance cell is formatted as hh:mm then remove the *24 from the Sum formula. :blush: | rad_s4 (7401) | ||
| 336589 | 2005-03-22 05:04:00 | Hi, I think rad_s4 has done a good job of giving you some options. Heres an alternative approach where A1:A20 is your list of times. To return as a day and a decimal.. =SUM(A1:A20) with formatting as a number To return as minutes... =SUM(A1:A20)*1440 with formatting as a number To return as hours and minutes... =SUM(A1:A20) with formatting as a custom format [H]:MM hth |
Parry (5696) | ||
| 336590 | 2005-03-23 00:16:00 | Thanks guys, that should give me what I need. Haven't had time to work on it fully yet. | lloyd (1076) | ||
| 1 | |||||