Time Clock Intervals

[Guest]

I would like to input the times punched from the time clock into Excel.
(That part is no problem.) Then, I'd like Excel to round each time to its
nearest 15 min. interval. Example: Brandi punches in at 8:26 - We round that
up to 8:30. She then punches out at 12:22 - We round that to 12:30. She
punches back in at 12:46 - Rounded to 12:45 and leaves at 5:57 - Rounded to
6:00.

I can't get Excel to do the rounding part. Please help.

 

[JE McGimpsey]

XL stores times as fractional days. Since there are 24*4 quarter hours
in a day, one way:

=ROUND(A1*96,0)/96

Format as time.

 

[Chip Pearson]

Laura,

You can use the MROUND function to round up or down to the nearest interval.
For example,

=MROUND(A1,TIME(0,15,0))

Be sure to format this cell as a time, not general or numeric. MROUND is
part of the Analysis Tool Pak, so you must load this. Go to the Tools menu,
choose Add Ins, and check "Analysis Tool Pak".


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)