minutes seconds days weeks years

[Pam Coleman]

I have a spreadsheet that in column A= seconds. I want to take the
seconds in col a and convert to minutes in col B, then in col C convert to
hours and in col D convert to days - this is the formula that was given to me
yesterday.
=a1/60 (in B1) would contain minutes
=b1/60 (in C1) would contain hours
=c1/24 (in D1) would contain days
Question now in col E I need to convert the days into weeks and in col F the
weeks into years. What would the formula be for this and also, what would
the formatting be, I do not want to round. I went into Custom and chose
hh:mm:ss, is this correct? Thanks,

 

[Bob Phillips]

E1: = D1/7
F1: =E1/52

these should all be formatted as General

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Pete_UK]

To answer the second part first: No, this would not be the correct
format to use. In B1 you would have a number which represented minutes
and decimal minutes (eg. 630 seconds is 10.5 minutes). Similarly, C1
would represent hours and decimal fractions of an hour, so a value of
1.25, for example, here would represent one and a quarter hours or 75
minutes.

If you want to show the seconds as a normal duration of hours, minutes
and seconds, then use this formula:

=A1/60/60/24

and format this cell using Custom format as [h]:mm:ss. The square
brackets around the h indicates to Excel that it should not wrap the
hours into days if they exceed 24.

Now to the first part: as there are 7 days in a week, then the formula
in E1 should be:

=D1/7

As for converting to years, the closest would be in F1:

=D1/365.25

but this is only an average over a number of years to take account of
leap years.

All your cells, B1 to F1, should be formatted as General or as Number
with 2 decimal places (to suit).

Hope this helps.

Pete