format second to hh:mm:ss should be simple but I'm stuck

[M.Siler]

I'm using Excel 2007 and this should be simple but I'm stuck. I have a
column of seconds and I want to see them formated as hh:mm:ss but when I do
the time format I get something that isn't correct. I've tried the TEXT,
CONVERT and several other.

Here's an example of what I'd expect:

185.4 should show 3:05

Thanks,
Mark

 

[Dave Peterson]

If you convert those values into seconds, you'll see it:

With the 185.4 in (say) A17:

=A17/24/60

And format as hh:mm

 

[M.Siler]

That works and I've also found if I use the TIME function.

185.4 in A17 I could use =TIME(,,A17) with the cell formatted as hh:mm:ss

I have a followup questions: How do I display the time in hh:mm:ss but if
there is no hours that I don't get the zeros?

 

[Dave Peterson]

One hour is equal to 1/24 (or 0.0416666666666667]

You can test that in a custom format:
[>=0.0416666666666667]hh:mm:ss;mm:ss

Personally, I would find this confusing. I'd want to see the same format (with
the hh=00) no matter what.

 

[M.Siler]

Thanks Dave, that work well. Let me ask something about how excel does
dates/time. If I enter the value 1 in a cell. Is that's 1/24 of a day,
correct? And from what you wrote below, 0.04166666666667 is one hour.

One hour is equal to 1/24 (or 0.0416666666666667]

You can test that in a custom format:
[>=0.0416666666666667]hh:mm:ss;mm:ss

Personally, I would find this confusing. I'd want to see the same format
(with
the hh=00) no matter what.

That works and I've also found if I use the TIME function.

185.4 in A17 I could use =TIME(,,A17) with the cell formatted as hh:mm:ss

I have a followup questions: How do I display the time in hh:mm:ss but
if
there is no hours that I don't get the zeros?

 

[Gord Dibben]

For some great info on how Excel stores and computes dats/times see Chip
Pearson's site.

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


Gord Dibben MS Excel MVP

Thanks Dave, that work well. Let me ask something about how excel does
dates/time. If I enter the value 1 in a cell. Is that's 1/24 of a day,
correct? And from what you wrote below, 0.04166666666667 is one hour.

One hour is equal to 1/24 (or 0.0416666666666667]

You can test that in a custom format:
[>=0.0416666666666667]hh:mm:ss;mm:ss

Personally, I would find this confusing. I'd want to see the same format
(with
the hh=00) no matter what.

That works and I've also found if I use the TIME function.

185.4 in A17 I could use =TIME(,,A17) with the cell formatted as hh:mm:ss

I have a followup questions: How do I display the time in hh:mm:ss but
if
there is no hours that I don't get the zeros?

If you convert those values into seconds, you'll see it:

With the 185.4 in (say) A17:

=A17/24/60

And format as hh:mm

:

I'm using Excel 2007 and this should be simple but I'm stuck. I have a
column of seconds and I want to see them formated as hh:mm:ss but when
I
do
the time format I get something that isn't correct. I've tried the
TEXT,
CONVERT and several other.

Here's an example of what I'd expect:

185.4 should show 3:05

Thanks,
Mark

 

[M.Siler]

That's a very helpful site. Thanks!

For some great info on how Excel stores and computes dats/times see Chip
Pearson's site.

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


Gord Dibben MS Excel MVP

Thanks Dave, that work well. Let me ask something about how excel does
dates/time. If I enter the value 1 in a cell. Is that's 1/24 of a day,
correct? And from what you wrote below, 0.04166666666667 is one hour.

One hour is equal to 1/24 (or 0.0416666666666667]

You can test that in a custom format:
[>=0.0416666666666667]hh:mm:ss;mm:ss

Personally, I would find this confusing. I'd want to see the same
format
(with
the hh=00) no matter what.

:

That works and I've also found if I use the TIME function.

185.4 in A17 I could use =TIME(,,A17) with the cell formatted as
hh:mm:ss

I have a followup questions: How do I display the time in hh:mm:ss but
if
there is no hours that I don't get the zeros?

If you convert those values into seconds, you'll see it:

With the 185.4 in (say) A17:

=A17/24/60

And format as hh:mm

:

I'm using Excel 2007 and this should be simple but I'm stuck. I have
a
column of seconds and I want to see them formated as hh:mm:ss but
when
I
do
the time format I get something that isn't correct. I've tried the
TEXT,
CONVERT and several other.

Here's an example of what I'd expect:

185.4 should show 3:05

Thanks,
Mark

 

[Dave Peterson]

Nope.

If you type 1 into a cell, it's 1 day (= 24 hours). Excel keeps track of dates
(and times) by just counting from a common starting date.

For most WinTel users, it's the number of days since Dec 31, 1899. For most Mac
users, it's Dec 31, 1903.

You can change the starting date via Tools|Options|Calculation tab (1904 date
system).

And times are just fractions of days.

So one hour =1/24 (so .75 = 3/4 = 18 hours = 18/24ths of a day)
And one minute =1/24/60

Chip Pearson goes into more details:
http://cpearson.com/excel/datetime.htm

Thanks Dave, that work well. Let me ask something about how excel does
dates/time. If I enter the value 1 in a cell. Is that's 1/24 of a day,
correct? And from what you wrote below, 0.04166666666667 is one hour.

One hour is equal to 1/24 (or 0.0416666666666667]

You can test that in a custom format:
[>=0.0416666666666667]hh:mm:ss;mm:ss

Personally, I would find this confusing. I'd want to see the same format
(with
the hh=00) no matter what.

That works and I've also found if I use the TIME function.

185.4 in A17 I could use =TIME(,,A17) with the cell formatted as hh:mm:ss

I have a followup questions: How do I display the time in hh:mm:ss but
if
there is no hours that I don't get the zeros?

If you convert those values into seconds, you'll see it:

With the 185.4 in (say) A17:

=A17/24/60

And format as hh:mm

:

I'm using Excel 2007 and this should be simple but I'm stuck. I have a
column of seconds and I want to see them formated as hh:mm:ss but when
I
do
the time format I get something that isn't correct. I've tried the
TEXT,
CONVERT and several other.

Here's an example of what I'd expect:

185.4 should show 3:05

Thanks,
Mark

 

[Dave Peterson]

I should have scrolled down <sigh>.

For some great info on how Excel stores and computes dats/times see Chip
Pearson's site.

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

Gord Dibben MS Excel MVP

Thanks Dave, that work well. Let me ask something about how excel does
dates/time. If I enter the value 1 in a cell. Is that's 1/24 of a day,
correct? And from what you wrote below, 0.04166666666667 is one hour.

One hour is equal to 1/24 (or 0.0416666666666667]

You can test that in a custom format:
[>=0.0416666666666667]hh:mm:ss;mm:ss

Personally, I would find this confusing. I'd want to see the same format
(with
the hh=00) no matter what.

:

That works and I've also found if I use the TIME function.

185.4 in A17 I could use =TIME(,,A17) with the cell formatted as hh:mm:ss

I have a followup questions: How do I display the time in hh:mm:ss but
if
there is no hours that I don't get the zeros?

If you convert those values into seconds, you'll see it:

With the 185.4 in (say) A17:

=A17/24/60

And format as hh:mm

:

I'm using Excel 2007 and this should be simple but I'm stuck. I have a
column of seconds and I want to see them formated as hh:mm:ss but when
I
do
the time format I get something that isn't correct. I've tried the
TEXT,
CONVERT and several other.

Here's an example of what I'd expect:

185.4 should show 3:05

Thanks,
Mark

 

[Gord Dibben]

You needed the practice<g>

And I still haven't activated my chellspecker.


Gord

I should have scrolled down <sigh>.

For some great info on how Excel stores and computes dats/times see Chip
Pearson's site.

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

Gord Dibben MS Excel MVP

Thanks Dave, that work well. Let me ask something about how excel does
dates/time. If I enter the value 1 in a cell. Is that's 1/24 of a day,
correct? And from what you wrote below, 0.04166666666667 is one hour.


One hour is equal to 1/24 (or 0.0416666666666667]

You can test that in a custom format:
[>=0.0416666666666667]hh:mm:ss;mm:ss

Personally, I would find this confusing. I'd want to see the same format
(with
the hh=00) no matter what.

:

That works and I've also found if I use the TIME function.

185.4 in A17 I could use =TIME(,,A17) with the cell formatted as hh:mm:ss

I have a followup questions: How do I display the time in hh:mm:ss but
if
there is no hours that I don't get the zeros?

If you convert those values into seconds, you'll see it:

With the 185.4 in (say) A17:

=A17/24/60

And format as hh:mm

:

I'm using Excel 2007 and this should be simple but I'm stuck. I have a
column of seconds and I want to see them formated as hh:mm:ss but when
I
do
the time format I get something that isn't correct. I've tried the
TEXT,
CONVERT and several other.

Here's an example of what I'd expect:

185.4 should show 3:05

Thanks,
Mark

 

[Harlan Grove]

For most WinTel users, it's the number of days since Dec 31, 1899. For most
Mac users, it's Dec 31, 1903.

....

Someone has to be picky.

1900 date system: numbers 61 to 2958465 (31 Dec 9999) are 60 plus the
number of days since 28 Feb 1900; numbers 1 to 59 are number of days
since 31 Dec 1899; 60 is fubar since 1900 wasn't a leap year.

1904 date system: numbers 0 to 2957003 (31 Dec 9999) are number of
days from 1 Jan 1904, *not* 31 Dec 1899 since 0 represents 1 Jan 1904
in this date system.