Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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. |
#2
|
|||
|
|||
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. |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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. |
#5
|
|||
|
|||
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Round UP Time | Excel Worksheet Functions | |||
Round Time | Excel Worksheet Functions | |||
Round Time | Excel Worksheet Functions | |||
time formatting and time categorizing (vlookup or data validation) | Excel Worksheet Functions | |||
How do I round time? | Excel Worksheet Functions |