Round current time

[Guest]

I have a timesheet with checkboxes. When the checkbox is checked it
automatically enters the current time into a cell. I need the time to be
rounded to the nearest five minutes...I have the time format set to show as
1:30 AM but seems to read 3/28/2006 9:45:55 AM...here is the formula I have
in the cell to include the current time: =IF(F18=TRUE,NOW(),"")....F18 refers
to the checkbox when checked. How can i get the time to round up to the next
5 minute interval? or can I? Any help would be greatly appreaciated. I thank
you in advance for your assistance!

 

[Peo Sjoblom]

To just get the time you can use

=IF(F18=TRUE,MOD(NOW(),1),"")

to roundup to nearest 5 minute

=IF(F18=TRUE,CEILING(MOD(NOW(),1),TIME(,5,)),"")

having said that you realize this is not a static time when the checkbox was
checked, it will always update
when the sheet is calculated? You can use circular reference or a macro to
get a static timestamp. example here

http://www.mcgimpsey.com/excel/timestamp.html

 

[Guest]

You my friend are a Rock Star!!! Worked like a charm! yes I was already aware
of the constant updating and have worked around that already...Just could not
figure out this part...thank you so very much! You Rock!
--
Randy Street
Rancho Cucamonga, CA

To just get the time you can use

=IF(F18=TRUE,MOD(NOW(),1),"")

to roundup to nearest 5 minute

=IF(F18=TRUE,CEILING(MOD(NOW(),1),TIME(,5,)),"")

having said that you realize this is not a static time when the checkbox was
checked, it will always update
when the sheet is calculated? You can use circular reference or a macro to
get a static timestamp. example here

http://www.mcgimpsey.com/excel/timestamp.html