Round Up Time IF

[carl]

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.

 

[hrlngrv]

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.

 

[JE McGimpsey]

One way:

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

 

[hrlngrv]

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.

 

[JE McGimpsey]

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.