seconds to minutes

[pavan5]

I'm trying to convert seconds to minutes in excel 2013, tried this by a2/86400 and then formatting the cell but it is not giving me desired result, I need something like this..
cell A2 cell B2
0 1
1 1
2 1
3 1
...
58 1
59 1
60 2
...
3599 59
3600 60 minutes and then back to 1 from next number so on

 

[dreid1011]

what I mean by round up means if its 30 minutes 37 seconds then this should be 31 minute and like wise if 30 minutes and 29 seconds it should 30 minute

Which is exactly what 63falcondude's formula in #7 does.

 

[pavan5]

Which is exactly what 63falcondude's formula in #7 does.

If I use that formula, =ROUND(A1/60,0), for 29 seconds it is giving 0 and 30th seconds it giving me 1 , but where as until 59th second it 0 minute...and for 89 seconds i should get 2 minutes (1.48333 rounding up, since 48 is greater than 30,if its for 75th second I should get 1 min as (1.2500) .25 is less than 29)

 

[dreid1011]

If I use that formula, =ROUND(A1/60,0), for 29 seconds it is giving 0 and 30th seconds it giving me 1 , but where as until 59th second it 0 minute...and for 89 seconds i should get 2 minutes (1.48333 rounding up, since 48 is greater than 30,if its for 75th second I should get 1 min as (1.2500) .25 is less than 29)

48 may be great than 30, but that 48 is in relation to 100, not 60. 48 is less than half of 100, and 30 is half of 60.

.25 would be equal to 15... in relation to 60 seconds.

So, 89 is 1 minute and 29 seconds. Therefore, the 29 should round down according to your requirements, not up.

The formula can be amended to account for less than 1 minute, but I think you're logic is slightly skewed.

 

[pavan5]

48 may be great than 30, but that 48 is in relation to 100, not 60. 48 is less than half of 100, and 30 is half of 60.

.25 would be equal to 15... in relation to 60 seconds.

So, 89 is 1 minute and 29 seconds. Therefore, the 29 should round down according to your requirements, not up.

The formula can be amended to account for less than 1 minute, but I think you're logic is slightly skewed.

yes you are right, pardon my language here...but your are correct 29 should round down and not up and like wise anything greater than 29 should round up, can this be achieved ?

 

[dreid1011]

yes you are right, pardon my language here...but your are correct 29 should round down and not up and like wise anything greater than 29 should round up, can this be achieved ?

Sure, and just one more question: values 0-60 should return 1 yes?

Edit: If the above is true, try this: =MAX(1,ROUND(B1/60,0))

 

[pavan5]

Sure, and just one more question: values 0-60 should return 1 yes?

Edit: If the above is true, try this: =MAX(1,ROUND(B1/60,0))

0-59 sec should return 0

 

[dreid1011]

0-59 sec should return 0

Then perhaps this:

=IF(A1<60,0,ROUND(A1/60,0))

 

[pavan5]

Then perhaps this:

=IF(A1<60,0,ROUND(A1/60,0))

thank you, is there a way we can define the rounding ? I mean it needs to be precise in 0-29 round down and 30-59 round up for the seconds that are not full minutes, I'm in media industry and series or shows are all in minutes .tats the reason am being stubborn about the rounding part

 

[dreid1011]

thank you, is there a way we can define the rounding ? I mean it needs to be precise in 0-29 round down and 30-59 round up for the seconds that are not full minutes, I'm in media industry and series or shows are all in minutes .tats the reason am being stubborn about the rounding part

I thought it was doing that. Which numbers exactly are causing trouble? (Just a few examples I suppose)

 

[pavan5]

I thought it was doing that. Which numbers exactly are causing trouble? (Just a few examples I suppose)

81st second = 1.35 this should be considered as 2 but this is show as 1
146th secont = 2.46 this should be considered as 3 but this is shown as 2