I have a table of approx 120 cells which contain what is supposed to be a
number of hours:mins:secs but in the formula bar a date and time is shown.
Example - cell shows
15:31:49. Formula is shown as 06/01/1900 07:31:49 . Cells are formatted
as custom (h):mm:ss and copy/paste-special - value throws up the result
6.31376157407407

The data forms part of an exercise to graphically illustrate the number of
minutes different departments spent on the phone each month using the
billing spreadsheets supplied by the telephone company and I need to convert
what is in each cell to a meaningful figure which can then be shown on a
graph.

Can anyone help?

Thanks

Barry

Format cell as Time 13:30:55

The cell will show as 7:31:49
The formula bar will show a date as well. Ignore the date.
--
Gary's Student


"Barry" wrote:

I have a table of approx 120 cells which contain what is supposed to be a
number of hours:mins:secs but in the formula bar a date and time is shown.
Example - cell shows
15:31:49. Formula is shown as 06/01/1900 07:31:49 . Cells are formatted
as custom (h):mm:ss and copy/paste-special - value throws up the result
6.31376157407407

The data forms part of an exercise to graphically illustrate the number of
minutes different departments spent on the phone each month using the
billing spreadsheets supplied by the telephone company and I need to convert
what is in each cell to a meaningful figure which can then be shown on a
graph.

Can anyone help?

Thanks

Barry

Barry

To see how Excel computes dates and times see Chip Pearson's site.

http://www.cpearson.com/excel/datetime.htm#AddingTimes


Gord Dibben Excel MVP

On Mon, 7 Nov 2005 23:47:43 -0000, "Barry"
wrote:

I have a table of approx 120 cells which contain what is supposed to be a
number of hours:mins:secs but in the formula bar a date and time is shown.
Example - cell shows
15:31:49. Formula is shown as 06/01/1900 07:31:49 . Cells are formatted
as custom (h):mm:ss and copy/paste-special - value throws up the result
6.31376157407407

The data forms part of an exercise to graphically illustrate the number of
minutes different departments spent on the phone each month using the
billing spreadsheets supplied by the telephone company and I need to convert
what is in each cell to a meaningful figure which can then be shown on a
graph.

Can anyone help?

Thanks

Barry