As Fluff pointed out changing the format does not change the underlying value, it just changes its appearance (imagine looking at an image through a filter, the original image doesn't change but how you perceive it does). If you don't mind working with a separate table it is probably easiest to just reference the cells with a formula of the cell reference +0 and custom format the cells as [hh]:mm:ss i.e.
Excel 2016 (Windows) 64 bit
| B | C | D | E | F | G | H | I | J | K | L | M | N | O |
---|
2 | Month | overtime1 | overtime2 | overtime3 | overtime4 | overtime5 | overtime6 | overtime7 | overtime8 | overtime9 | overtime10 | overtime11 | overtime12 | overtime13 |
3 | JAN | 02:05:42 | | | 01:35:56 | 00:07:17 | | | | | | | 07:30:30 | 06:33:33 |
4 | FEB | 01:57:24 | | | 02:40:44 | 00:01:26 | | 00:06:08 | | | | | 07:19:54 | 09:16:22 |
5 | MAR | 02:45:55 | | | 02:46:47 | 02:07:54 | | | | | | | 12:11:56 | 12:20:01 |
6 | APR | 24:48:53 | 19:56:50 | 05:12:23 | 04:00:25 | 00:06:36 | 01:07:00 | 00:25:35 | | | | | 06:36:53 | 11:18:38 |
7 | MAY | 04:14:15 | 15:15:33 | 00:49:24 | 03:15:18 | 00:28:34 | | 00:02:38 | | | | | 06:44:49 | 08:26:00 |
8 | JUN | 01:13:55 | 04:43:37 | 00:17:23 | 02:12:52 | 00:13:08 | 00:17:42 | 00:28:46 | 05:56:13 | 06:42:38 | 09:52:26 | 23:27:29 | 13:15:58 | 08:41:48 |
9 | JUL | 00:19:37 | 00:49:47 | 00:11:02 | 01:25:45 | | 00:07:43 | 00:02:58 | 06:07:22 | 02:44:48 | 10:18:06 | 20:18:21 | 11:16:34 | 06:46:22 |
10 | AUG | 00:28:04 | 01:10:38 | 00:11:37 | 00:49:18 | | 00:05:54 | 00:16:12 | 01:24:53 | 00:55:50 | 01:45:14 | 03:11:03 | 07:30:55 | 06:25:28 |
11 | SEP | 00:19:04 | 01:00:05 | 00:02:11 | 00:35:51 | | 00:03:53 | 00:08:47 | 00:39:35 | 00:06:47 | 00:19:39 | 01:00:19 | 07:42:45 | 07:15:14 |
12 | OCT | 00:07:50 | 00:51:07 | 00:07:04 | 00:43:52 | | | | 00:32:35 | 00:09:38 | 00:08:17 | 00:32:07 | 08:26:28 | 05:39:39 |
13 | NOV | 00:06:43 | 00:33:39 | 00:04:33 | 00:34:12 | | | | 00:06:45 | 00:19:50 | 00:26:34 | 01:21:36 | 07:44:18 | 06:52:32 |
14 | DEC | 01:36:06 | 00:17:24 | | 00:24:50 | | | 00:00:25 | 00:03:43 | 00:02:13 | 00:03:24 | 00:05:15 | 07:10:49 | 05:48:25 |
15 | Total | 40:03:28 | 44:38:40 | 06:55:37 | 21:05:50 | 03:04:55 | 01:42:12 | 01:31:29 | 14:51:06 | 11:01:44 | 22:53:40 | 49:56:10 | 103:31:49 | 95:24:02 |
16 | | | | | | | | | | | | | | |
17 | Month | overtime1 | overtime2 | overtime3 | overtime4 | overtime5 | overtime6 | overtime7 | overtime8 | overtime9 | overtime10 | overtime11 | overtime12 | overtime13 |
18 | JAN | 02:05:42 | 00:00:00 | 00:00:00 | 01:35:56 | 00:07:17 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 07:30:30 | 06:33:33 |
19 | FEB | 01:57:24 | 00:00:00 | 00:00:00 | 02:40:44 | 00:01:26 | 00:00:00 | 00:06:08 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 07:19:54 | 09:16:22 |
20 | MAR | 02:45:55 | 00:00:00 | 00:00:00 | 02:46:47 | 02:07:54 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 12:11:56 | 12:20:01 |
21 | APR | 24:48:53 | 19:56:50 | 05:12:23 | 04:00:25 | 00:06:36 | 01:07:00 | 00:25:35 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 06:36:53 | 11:18:38 |
22 | MAY | 04:14:15 | 15:15:33 | 00:49:24 | 03:15:18 | 00:28:34 | 00:00:00 | 00:02:38 | 00:00:00 | 00:00:00 | 00:00:00 | 00:00:00 | 06:44:49 | 08:26:00 |
23 | JUN | 01:13:55 | 04:43:37 | 00:17:23 | 02:12:52 | 00:13:08 | 00:17:42 | 00:28:46 | 05:56:13 | 06:42:38 | 09:52:26 | 23:27:29 | 13:15:58 | 08:41:48 |
24 | JUL | 00:19:37 | 00:49:47 | 00:11:02 | 01:25:45 | 00:00:00 | 00:07:43 | 00:02:58 | 06:07:22 | 02:44:48 | 10:18:06 | 20:18:21 | 11:16:34 | 06:46:22 |
25 | AUG | 00:28:04 | 01:10:38 | 00:11:37 | 00:49:18 | 00:00:00 | 00:05:54 | 00:16:12 | 01:24:53 | 00:55:50 | 01:45:14 | 03:11:03 | 07:30:55 | 06:25:28 |
26 | SEP | 00:19:04 | 01:00:05 | 00:02:11 | 00:35:51 | 00:00:00 | 00:03:53 | 00:08:47 | 00:39:35 | 00:06:47 | 00:19:39 | 01:00:19 | 07:42:45 | 07:15:14 |
27 | OCT | 00:07:50 | 00:51:07 | 00:07:04 | 00:43:52 | 00:00:00 | 00:00:00 | 00:00:00 | 00:32:35 | 00:09:38 | 00:08:17 | 00:32:07 | 08:26:28 | 05:39:39 |
28 | NOV | 00:06:43 | 00:33:39 | 00:04:33 | 00:34:12 | 00:00:00 | 00:00:00 | 00:00:00 | 00:06:45 | 00:19:50 | 00:26:34 | 01:21:36 | 07:44:18 | 06:52:32 |
29 | DEC | 01:36:06 | 00:17:24 | 00:00:00 | 00:24:50 | 00:00:00 | 00:00:00 | 00:00:25 | 00:03:43 | 00:02:13 | 00:03:24 | 00:05:15 | 07:10:49 | 05:48:25 |
30 | Total | 40:03:28 | 44:38:40 | 06:55:37 | 21:05:50 | 03:04:55 | 01:42:12 | 01:31:29 | 14:51:06 | 11:01:44 | 22:53:40 | 49:56:10 | 103:31:49 | 95:24:02 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
OT
Worksheet Formulas
Cell | Formula |
---|
B17 | =B2 |
---|
C17 | =C2 |
---|
B18 | =B3 |
---|
C18 | =C3+0 |
---|
C30 | =SUM([COLOR=rgb(255]C18:C29[/COLOR]) |
---|
<thead>
</thead><tbody>
</tbody> |
<tbody>
</tbody>