software

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(),quot;quot;)....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!
--
Randy Street
Rancho Cucamonga, CA

To just get the time you can use

=IF(F18=TRUE,MOD(NOW(),1),quot;quot;)

to roundup to nearest 5 minute

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

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

www.mcgimpsey.com/excel/timestamp.htmlquot;Randyquot; gt; wrote in message
...
gt;I have a timesheet with checkboxes. When the checkbox is checked it
gt; automatically enters the current time into a cell. I need the time to be
gt; rounded to the nearest five minutes...I have the time format set to show
gt; as
gt; 1:30 AM but seems to read 3/28/2006 9:45:55 AM...here is the formula I
gt; have
gt; in the cell to include the current time: =IF(F18=TRUE,NOW(),quot;quot;)....F18
gt; refers
gt; to the checkbox when checked. How can i get the time to round up to the
gt; next
gt; 5 minute interval? or can I? Any help would be greatly appreaciated. I
gt; thank
gt; you in advance for your assistance!
gt; --
gt; Randy Street
gt; Rancho Cucamonga, CA
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, CAquot;Peo Sjoblomquot; wrote:

gt; To just get the time you can use
gt;
gt; =IF(F18=TRUE,MOD(NOW(),1),quot;quot;)
gt;
gt; to roundup to nearest 5 minute
gt;
gt; =IF(F18=TRUE,CEILING(MOD(NOW(),1),TIME(,5,)),quot;quot;)
gt;
gt; having said that you realize this is not a static time when the checkbox was
gt; checked, it will always update
gt; when the sheet is calculated? You can use circular reference or a macro to
gt; get a static timestamp. example here
gt;
gt; www.mcgimpsey.com/excel/timestamp.html
gt;
gt;
gt; quot;Randyquot; gt; wrote in message
gt; ...
gt; gt;I have a timesheet with checkboxes. When the checkbox is checked it
gt; gt; automatically enters the current time into a cell. I need the time to be
gt; gt; rounded to the nearest five minutes...I have the time format set to show
gt; gt; as
gt; gt; 1:30 AM but seems to read 3/28/2006 9:45:55 AM...here is the formula I
gt; gt; have
gt; gt; in the cell to include the current time: =IF(F18=TRUE,NOW(),quot;quot;)....F18
gt; gt; refers
gt; gt; to the checkbox when checked. How can i get the time to round up to the
gt; gt; next
gt; gt; 5 minute interval? or can I? Any help would be greatly appreaciated. I
gt; gt; thank
gt; gt; you in advance for your assistance!
gt; gt; --
gt; gt; Randy Street
gt; gt; Rancho Cucamonga, CA
gt;
gt;
gt;