convert day:hour:min (1:3:40) to just hours only or convert into a format transferrable to

1) may i know the generic formula to convert day:hour:min (1:3:40) to just hours only?


2) else the reason why i am changing the format is because i have a duration column consisting of data in day:hour:min format which creates some formatting error UPON using that column in a pivot table! HELP! what was generated was as shown below when i put that column in the 'column label' of pivot table:

12:35:00 AM 12:38:00 AM 12:41:00 AM 12:42:00 AM 12:44:00 AM 12:47:00 AM 12:54:00 AM 12:56:00 AM 12:58:00 AM 1:02:00 AM 1:07:00 AM 1:08:00 AM 1:09:00 AM 1:14:00 AM 1:16:00 AM 1:18:00 AM 1:21:00 AM 1:22:00 AM 1:27:00 AM 1:29:00 AM 1:34:00 AM 1:37:00 AM 1:42:00 AM 1:43:00 AM 1:46:00 AM 1:47:00 AM 1:50:00 AM 1:52:00 AM 1:55:00 AM 1:56:00 AM 1:59:00 AM 2:02:00 AM 2:04:00 AM 2:06:00 AM 2:09:00 AM 2:13:00 AM 2:18:00 AM 2:19:00 AM 2:21:00 AM 2:27:00 AM 2:29:00 AM 2:36:00 AM 2:37:00 AM 2:38:00 AM 2:39:00 AM 2:41:00 AM 2:44:00 AM 2:46:00 AM 2:52:00 AM 2:53:00 AM 2:54:00 AM 2:57:00 AM 2:58:00 AM 2:59:00 AM 3:02:00 AM 3:10:00 AM 3:11:00 AM 3:13:00 AM 3:14:00 AM 3:17:00 AM 3:18:00 AM 3:19:00 AM 3:22:00 AM 3:23:00 AM 3:24:00 AM 3:28:00 AM 3:29:00 AM 3:30:00 AM 3:31:00 AM 3:32:00 AM 3:33:00 AM 3:34:00 AM 3:38:00 AM 3:52:00 AM 3:53:00 AM 3:54:00 AM 3:55:00 AM 3:56:00 AM 3:57:00 AM 4:03:00 AM 4:05:00 AM 4:10:00 AM 4:28:00 AM 4:31:00 AM 4:38:00 AM 4:50:00 AM 4:55:00 AM 4:56:00 AM 4:58:00 AM 5:04:00 AM 5:10:00 AM 5:11:00 AM 5:15:00 AM 5:17:00 AM 5:19:00 AM 5:31:00 AM 5:40:00 AM 5:43:00 AM 6:05:00 AM 6:10:00 AM 6:13:00 AM 7:29:00 AM 5:06:00 PM 5:10:00 PM 5:14:00 PM 5:22:00 PM 5:25:00 PM 5:27:00 PM 5:28:00 PM 5:32:00 PM 5:41:00 PM 5:52:00 PM 5:53:00 PM 6:10:00 PM 6:11:00 PM 6:13:00 PM 6:15:00 PM 6:18:00 PM 6:19:00 PM 6:20:00 PM 6:25:00 PM 6:28:00 PM 6:31:00 PM 6:36:00 PM 6:38:00 PM 6:44:00 PM 6:52:00 PM 6:54:00 PM 6:56:00 PM 6:57:00 PM 7:00:00 PM 7:01:00 PM 7:03:00 PM 7:05:00 PM 7:07:00 PM 7:09:00 PM 7:10:00 PM 7:12:00 PM 7:13:00 PM 7:15:00 PM 7:30:00 PM 7:34:00 PM 7:39:00 PM 7:42:00 PM 7:48:00 PM 7:55:00 PM 7:56:00 PM 8:09:00 PM 8:11:00 PM 8:26:00 PM 8:30:00 PM 8:43:00 PM 8:47:00 PM 8:55:00 PM 8:56:00 PM 8:58:00 PM 9:03:00 PM 9:05:00 PM 9:06:00 PM 9:12:00 PM 9:15:00 PM 9:48:00 PM 9:53:00 PM 10:00:00 PM 10:39:00 PM 11:13:00 PM 11:37:00 PM 11:39:00 PM 11:41:00 PM 11:47:00 PM 11:49:00 PM 11:50:00 PM 11:52:00 PM 1/1/1900 0:09 1/1/1900 0:13 1/1/1900 0:14 1/1/1900 0:20 1/1/1900 0:21 1/1/1900 0:22 1/1/1900 0:23 1/1/1900 0:35 1/1/1900 0:57 1/1/1900 0:58 1/1/1900 0:59 1/1/1900 1:00 1/1/1900 1:03 1/1/1900 1:05 1/1/1900 1:08 1/1/1900 1:10 1/1/1900 1:12 1/1/1900 1:13 1/1/1900 1:14 1/1/1900 1:16 1/1/1900 1:17 1/1/1900 1:18 1/1/1900 1:26 1/1/1900 1:29 1/1/1900 1:48 1/1/1900 1:56 1/1/1900 2:21 1/1/1900 2:27 1/1/1900 2:40 1/1/1900 2:54 1/1/1900 3:07 1/1/1900 4:43 1/1/1900 4:48 1/1/1900 18:21 1/1/1900 18:23 1/1/1900 18:29 1/1/1900 18:45 1/1/1900 18:46 1/1/1900 18:47 1/1/1900 18:48 1/1/1900 18:49 1/1/1900 19:00 1/1/1900 19:03 1/1/1900 19:15 1/1/1900 19:33 1/1/1900 19:35 1/1/1900 19:43 1/1/1900 20:05 1/1/1900 20:13 1/1/1900 20:18 1/1/1900 20:23 1/1/1900 20:51 1/1/1900 21:02 1/1/1900 21:03 1/1/1900 21:20 1/1/1900 21:21 1/1/1900 21:22 1/1/1900 21:23 1/1/1900 21:31 1/1/1900 22:36 1/1/1900 23:06 1/1/1900 23:17 1/1/1900 23:21 1/1/1900 23:23 1/1/1900 23:32 1/1/1900 23:34 1/1/1900 23:41 2/1/1900 0:05 2/1/1900 0:10 2/1/1900 0:34 2/1/1900 0:42 2/1/1900 0:46 2/1/1900 0:50 2/1/1900 18:16 2/1/1900 18:22


having said that may i know what i should do to attain a column of duration in hours or day:hours:min (shown below) whichever is possible to make my pivot table work? thanks! below is what i hope to see when i add to "column label" in the pivot table and not the above. Hope someone can help me in this!

0:5:6
0:4:21
0:22:19
0:21:56
0:5:40
0:4:56
0:6:16
1:7:6
0:4:34
0:4:25
0:3:58
0:5:6
0:4:21
0:22:19
0:21:56
0:5:40
0:4:56
0:6:16
1:7:6
0:4:34
0:4:25
0:3:58
0:5:6
0:4:21
0:22:19
0:21:56
0:5:40
0:4:56
0:6:16
1:7:6
0:4:34
0:4:25
0:3:58

Hi.

Please provide a sample file showing your expected results manually.

Srinivas

hi i have uploaded the excel data.

I would wish to, on the pivot table tab, insert a column along column B stating the turnaround time for the respective timings of the dates listed on column A for each respective modality. In other words I have difficulty transferring the values highlighted in yellow in the 'combined modalities' tab, ie the turnaround time values, to the pivot table! some one please assist me and its gladly appreciated! =)