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 , "carl" 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. |
|
All times are GMT +1. The time now is 07:44 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com