Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
minutes seconds days weeks years
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, If |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
minutes seconds days weeks years
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) "Pam Coleman" wrote in message ... 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, If |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
minutes seconds days weeks years
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 Pam Coleman wrote: 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, If |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do I calculate times in seconds and minutes using percentages | Excel Worksheet Functions | |||
How do I sum increments of minutes and seconds | Excel Worksheet Functions | |||
How to calculate in weeks and days? | Excel Discussion (Misc queries) | |||
adding rows of hours and minutes to get a total | Excel Worksheet Functions | |||
convert seconds to minutes and seconds | Excel Worksheet Functions |