Thread: Round Up Time IF

Hi. I have another roundup question dealing with time.

Can a roundup formula do the following:

If my time is past the 30 second threshhold, round up to the next whole
minute plas 1 minute. If the time os not past the 30 second threshold, round
up to the nearest minute. For example:

1:23:33 PM rounds up to 1:25:00 PM
1.23:15 PM rounds up to 1:24:00 PM

Thank you in advance again.

carl wrote...
....
>If my time is past the 30 second threshhold, round up to the next
whole
>minute plas 1 minute. If the time os not past the 30 second threshold,
round
>up to the nearest minute. For example:
>
>1:23:33 PM rounds up to 1:25:00 PM
>1.23:15 PM rounds up to 1:24:00 PM

If 01:23:00 PM would round up to 01:24:00 PM, then try

=ROUND(x*1440+1,0)/1440

Otherwise, if 01:23:00 PM would remain 01:23:00 PM but 01:23:01 PM
would round up to 01:24:00 PM, try

=ROUND(x*1440+(SECOND(x)>0),0)/1440

Note: 1440 is the number of minutes in a day, and times are stored as
fractions of days.

One way:

=CEILING(NOW()+"0:0:29","0:1")

In article <EA1346D9-10F6-43C7-8A7A-C9611CAF0F84@microsoft.com>,
"carl" <carl@discussions.microsoft.com> wrote:

> Hi. I have another roundup question dealing with time.
>
> Can a roundup formula do the following:
>
> If my time is past the 30 second threshhold, round up to the next whole
> minute plas 1 minute. If the time os not past the 30 second threshold, round
> up to the nearest minute. For example:
>
> 1:23:33 PM rounds up to 1:25:00 PM
> 1.23:15 PM rounds up to 1:24:00 PM

JE McGimpsey wrote...
>One way:
>
> =CEILING(NOW()+"0:0:29","0:1")
....

Quibble: this might depend on locale-specific time format. Also assumes
NOW() always returns values rounded to whole seconds. It doesn't.

In article <1105123887.530081.260110@f14g2000cwb.googlegroups.com>,
hrlngrv@aol.com wrote:

> Also assumes NOW() always returns values rounded to whole seconds. It
> doesn't.

That's apparently version-dependent. MacXL returns whole seconds, XL03
returns (approximately) hundredths.

Thanks for pointing that out - I hadn't tripped on it before.