time formula
 

Hi all,

(office 97)

You guys helped me the other day with some formula where I wanted to convert
say a figure of 93 into seconds. This was done & works fine, the formula was
as follows;

In cell A2 enter =INT(A1/60) + MOD(A1,60)/100

I now need help changing the format slightly.

Say for example there was a figure of 3666, I need to make the formula
diaplay as 01:01:06 ie 1 hour, 1 min & 6 secs
Have set the cell format to the above.

If you have any ideas it would be appreciated as I am out of my depth

Thanks again in advance

Hi

when 3666 is in cell A2
Into another cell enter the formula
=A2/(24*60*60)
and format as "hh:mm:ss" (or "[h]:mm:ss" when time can override 24h)

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"James" wrote in message
...
Hi all,

(office 97)

You guys helped me the other day with some formula where I wanted to
convert
say a figure of 93 into seconds. This was done & works fine, the formula
was
as follows;

In cell A2 enter =INT(A1/60) + MOD(A1,60)/100

I now need help changing the format slightly.

Say for example there was a figure of 3666, I need to make the formula
diaplay as 01:01:06 ie 1 hour, 1 min & 6 secs
Have set the cell format to the above.

If you have any ideas it would be appreciated as I am out of my depth

Thanks again in advance

Arvi,

Thanks for that - 1 last thing can you explain how this formula works, ie;

are you multiplying 24 hrs by 60 mins by 60 secs then dividing by the
number of seconds in cell A2?

"Arvi Laanemets" wrote:

Hi

when 3666 is in cell A2
Into another cell enter the formula
=A2/(24*60*60)
and format as "hh:mm:ss" (or "[h]:mm:ss" when time can override 24h)

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets


"James" wrote in message
...
Hi all,

(office 97)

You guys helped me the other day with some formula where I wanted to
convert
say a figure of 93 into seconds. This was done & works fine, the formula
was
as follows;

In cell A2 enter =INT(A1/60) + MOD(A1,60)/100

I now need help changing the format slightly.

Say for example there was a figure of 3666, I need to make the formula
diaplay as 01:01:06 ie 1 hour, 1 min & 6 secs
Have set the cell format to the above.

If you have any ideas it would be appreciated as I am out of my depth

Thanks again in advance

Hi


"James" wrote in message
...
Arvi,

Thanks for that - 1 last thing can you explain how this formula works, ie;

are you multiplying 24 hrs by 60 mins by 60 secs then dividing by the
number of seconds in cell A2?

Exctly that.

Date and time in Excel are really both same data type, a datetime. The date
is integer part od datetime, and time is decimal part. One day is really a
number 1. You had the number of seconds in cell, dividing it with 60 give
number of minutes, dividing again gives number of hours, and at last
dividing with 24 gives number of days (in your case it is
0.0424305555555556, which in time format is displayed as 01:01:06).

PS. I used (24*60*60) in formula to make it easier to understand. You can
replace it with 86400.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets

Thankyou- I understand

Cheers


"Arvi Laanemets" wrote:

Hi


"James" wrote in message
...
Arvi,

Thanks for that - 1 last thing can you explain how this formula works, ie;

are you multiplying 24 hrs by 60 mins by 60 secs then dividing by the
number of seconds in cell A2?

Exctly that.

Date and time in Excel are really both same data type, a datetime. The date
is integer part od datetime, and time is decimal part. One day is really a
number 1. You had the number of seconds in cell, dividing it with 60 give
number of minutes, dividing again gives number of hours, and at last
dividing with 24 gives number of days (in your case it is
0.0424305555555556, which in time format is displayed as 01:01:06).

PS. I used (24*60*60) in formula to make it easier to understand. You can
replace it with 86400.

--
When sending mail, use address arvil<attarkon.ee
Arvi Laanemets