Duration

[Guest]

I have a field in time format of hh:mm:ss, which I've custom formated to [m]
to get total duration minutes. My problem is I need the seconds to round up
if they are more than 30. Is there a way to do this? I use this field for
conference call billing, if that helps.

Example:

47:05:35 formated to [m] reads 2825 and I need it to read 2826

 

[Guest]

You can use a help column, since you want 30 seconds to round down you can use

=IF(MOD(A1*1440,1)>0.5,ROUND(A1*1440,0.5),FLOOR(A1*1440,1))

if you want regular rounding rules

=ROUND(A1*1440,0.5)

which means 30 secs (or 0.5 minute) rounds up and everything less rounds down

Regards,

Peo Sjoblom

 

[Guest]

Lovely! I just needed the "regular rounding rules". Thank you so much!

You can use a help column, since you want 30 seconds to round down you can use

=IF(MOD(A1*1440,1)>0.5,ROUND(A1*1440,0.5),FLOOR(A1*1440,1))

if you want regular rounding rules

=ROUND(A1*1440,0.5)

which means 30 secs (or 0.5 minute) rounds up and everything less rounds down

Regards,

Peo Sjoblom

I have a field in time format of hh:mm:ss, which I've custom formated to [m]
to get total duration minutes. My problem is I need the seconds to round up
if they are more than 30. Is there a way to do this? I use this field for
conference call billing, if that helps.

Example:

47:05:35 formated to [m] reads 2825 and I need it to read 2826