Calculating daily timesheet to nearest 1/4 hour

[ddsdarley]

I have a timesheet showing time in and time out. I need my formula to figure time to the nearest 1/4 hour and results be in decimal format.
Example:
time in: 7:39 AM (Cell B1)
time out: 3:32 PM (Cell C1)
Need formula to show results as hours 7.75 (Cell D1)

Thanks in advance for any help!

 

[T. Valko]

I have a timesheet showing time in and time out. I need my formula to figure time to the nearest 1/4 hour and results be in decimal format.
Example:
time in: 7:39 AM (Cell B1)
time out: 3:32 PM (Cell C1)
Need formula to show results as hours 7.75 (Cell D1)

Thanks in advance for any help!

Can you explain why the result should be 7.75?

The difference in time is 7:53. The nearest 15 minute interval would be 8:00. We can convert to decimal value after we figure out the correct rounding method.

 

[Jonmo1]

Welcome to the board..

I'm sure this can be done, but some clarification is needed..

Do you want the RESULT rounded, or do you want both start and end rounded, then find the difference?

3:32PM - 7:39AM = 7 hours 53 minutes, so nearest quarter hour would be 8 hours.
or
3:32 rounded to nearest quarter is 3:30
7:39 rounded to nearest quarter is 7:45
Then 3:30 - 7:45 = 7 hours 45 minutes

 

[Scott Huish]

This is how I interpreted the question:

=(ROUNDDOWN((C1-B1)*96,0)/96)*24

 

[ddsdarley]

I do want the start and end times to round to the nearest 1/4 hour. HOTPEPPER's formula gives me the result I'm looking for. Thanks to each of you for your help!

 

[barry houdini]

Hotpepper's formula isn't rounding both start and end time, it happens to work for your example but I'm not sure it'll work in all cases, e.g. if you have start time 08:05 and end time 11:25 then presumably the times should be rounded to 08:00 and 11:30 respectively so the result would be 3.5......Hotpepper's suggestion will give you 3.25

To round both start and end time to the nearest ¼ hour then get the difference as a decimal try this version

=(ROUND(C1*96,0)-ROUND(B1*96,0))/4

assumes that both times will be on the same day

 

[ddsdarley]

I've gotten myself confused so I'm showing my spreadsheet with the info I need.

<TABLE style="WIDTH: 545pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=729><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=8 width=75><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #c2d69a; WIDTH: 56pt; HEIGHT: 47.25pt; BORDER-TOP: #17375d 1.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl79 height=63 rowSpan=2 width=75>Date</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c2d69a; WIDTH: 56pt; BORDER-TOP: #17375d 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl81 rowSpan=2 width=75>Day</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c2d69a; WIDTH: 112pt; BORDER-TOP: #17375d 1.5pt solid; BORDER-RIGHT: black 1.5pt solid" class=xl74 width=150 colSpan=2>Regular</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c2d69a; WIDTH: 112pt; BORDER-TOP: #17375d 1.5pt solid; BORDER-RIGHT: #f0f0f0" class=xl74 width=150 colSpan=2>Hourly Breakdown</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #c2d69a; WIDTH: 56pt; BORDER-TOP: #17375d 1.5pt solid; BORDER-RIGHT: windowtext 1.5pt solid" class=xl83 rowSpan=2 width=75>Comp Used</TD><TD style="BORDER-BOTTOM: black 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #c2d69a; WIDTH: 56pt; BORDER-TOP: #17375d 1.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl77 rowSpan=2 width=75>Total Hrs.</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 97pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63 width=129></TD></TR><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c2d69a; HEIGHT: 30.75pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=41>In</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl66>Out</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c2d69a; WIDTH: 56pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=75>Regular Hrs.</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c2d69a; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64>Comp</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63></TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #fde9d9; HEIGHT: 20.1pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=26 align=right>8/1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl68>Monday</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right>8:09 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 align=right>7:06 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>8.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>2.75</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>10.75</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63></TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #fde9d9; HEIGHT: 20.1pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl72 height=26 align=right>8/2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl68>Tuesday</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 align=right>10:04 AM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1.5pt solid" class=xl70 align=right>3:30 PM</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>5.43</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>-0.18</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: #f0f0f0" class=xl73></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; BACKGROUND-COLOR: #fde9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl71 align=right>5.25</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl63>Should Be 5.5</TD></TR></TBODY></TABLE>

I need
E3 - Regular Hours to show anything worked up to 8 hours rounded to the nearest 1/4 hour.
Formula in E3: =IF((D3-C3)*24 > 8,8,((D3-C3))*24)

I need F3 - Comp Hours to show anything over 8 hours rounded to the nearest 1/4 hour.
Formula in F3: =(ROUNDDOWN((D3-C3)*96,0)/96)*24-E3

G3 Comp Hrs. Used will be manually entered to reflect any hours used rounded to the nearest 1/4 hour.

H3 Total Hrs. will be E3+F3-G3
Formula in H3: =(ROUNDDOWN((D3-C3)*96,0)/96)*24-(G3)

Sorry I didn't give enough details earlier, but maybe this gives a better understanding.

 

[T. Valko]

I've gotten myself confused so I'm showing my spreadsheet with the info I need.

<TABLE style="WIDTH: 545pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=729 border=0><COLGROUP><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=8 width=75><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><TBODY><TR style="HEIGHT: 16.5pt" height=22><TD class=xl79 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #17375d 1.5pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; HEIGHT: 47.25pt; BACKGROUND-COLOR: #c2d69a" width=75 height=63 rowSpan=2>Date</TD><TD class=xl81 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #17375d 1.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #c2d69a" width=75 rowSpan=2>Day</TD><TD class=xl74 style="BORDER-RIGHT: black 1.5pt solid; BORDER-TOP: #17375d 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 112pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c2d69a" width=150 colSpan=2>Regular</TD><TD class=xl74 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #17375d 1.5pt solid; BORDER-LEFT: windowtext; WIDTH: 112pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c2d69a" width=150 colSpan=2>Hourly Breakdown</TD><TD class=xl83 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: #17375d 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #c2d69a" width=75 rowSpan=2>Comp Used</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: #17375d 1.5pt solid; BORDER-LEFT: windowtext 1.5pt solid; WIDTH: 56pt; BORDER-BOTTOM: black 0.5pt solid; BACKGROUND-COLOR: #c2d69a" width=75 rowSpan=2>Total Hrs.</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; WIDTH: 97pt; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent" width=129></TD></TR><TR style="HEIGHT: 30.75pt; mso-height-source: userset" height=41><TD class=xl65 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 30.75pt; BACKGROUND-COLOR: #c2d69a" height=41>In</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c2d69a">Out</TD><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; WIDTH: 56pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c2d69a" width=75>Regular Hrs.</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: #f0f0f0; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #c2d69a">Comp</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.1pt; BACKGROUND-COLOR: #fde9d9" align=right height=26>8/1</TD><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9">Monday</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9" align=right>8:09 AM</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9" align=right>7:06 PM</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9" align=right>8.00</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9" align=right>2.75</TD><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9" align=right>10.75</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent"></TD></TR><TR style="HEIGHT: 20.1pt; mso-height-source: userset" height=26><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 20.1pt; BACKGROUND-COLOR: #fde9d9" align=right height=26>8/2</TD><TD class=xl68 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9">Tuesday</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9" align=right>10:04 AM</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 1.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9" align=right>3:30 PM</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9" align=right>5.43</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9" align=right>-0.18</TD><TD class=xl73 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9"></TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #fde9d9" align=right>5.25</TD><TD class=xl63 style="BORDER-RIGHT: #f0f0f0; BORDER-TOP: #f0f0f0; BORDER-LEFT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; BACKGROUND-COLOR: transparent">Should Be 5.5</TD></TR></TBODY></TABLE>

I need
E3 - Regular Hours to show anything worked up to 8 hours rounded to the nearest 1/4 hour.
Formula in E3: =IF((D3-C3)*24 > 8,8,((D3-C3))*24)

I need F3 - Comp Hours to show anything over 8 hours rounded to the nearest 1/4 hour.
Formula in F3: =(ROUNDDOWN((D3-C3)*96,0)/96)*24-E3

G3 Comp Hrs. Used will be manually entered to reflect any hours used rounded to the nearest 1/4 hour.

H3 Total Hrs. will be E3+F3-G3
Formula in H3: =(ROUNDDOWN((D3-C3)*96,0)/96)*24-(G3)

Sorry I didn't give enough details earlier, but maybe this gives a better understanding.

Will the time ever span past midnight? For example:

In: 10:05 PM
Out: 3:10 AM

 

[Sam770]

I'm looking for the exact thing and came up with two possible formulas. (Note* I am NOT an expert in this at all)
"A" IS TIME IN
"B" IS TIME OUT
[This does not take into account any night shifts)
=ROUNDUP((B1-A1)*24*4,0)/4
OR
=ROUNDUP((B1-A1)*96,0)/96*24