Round time to nearest 15 min increments

[NKMS]

Hi
This is my first time in a forum and hope someone is able to assist me please .... I am currently building a timesheet for a client to (1) enable field staff to enter simple start and finish times (eg 8.23am to 5.23pm) and (2) incorporate backend formulas to apply rounding rules for 15 min and then decimal conversion for auto upload to the payroll system. As I am working in a developing country, I need keep it simple please for future spreadsheet maintenance.

The one step which has driven me crazy today is establishing the formula to calculate the following:

(a) start time
- any time before 8am results = 8.00 and any time after 8am is rounded up to the nearest 15 minutes (eg 8.12am = 8.15am)

(b) finish time
- finish time is rounded down to the nearest 15 minutes (eg 4.56pm = 4.45).

Please see example in red below:

<TABLE style="WIDTH: 425pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=566><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 89pt; mso-width-source: userset; mso-width-alt: 4209" width=118><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" span=2 width=64><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 height=20 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 89pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=118></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>AM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=64>?</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=64></TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl67 width=64>PM</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl68 width=64>?</TD><TD style="BORDER-BOTTOM: #f0f0f0; BORDER-LEFT: #f0f0f0; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0" class=xl66 width=64></TD></TR><TR style="HEIGHT: 108.6pt; mso-height-source: userset" height=145><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; WIDTH: 48pt; HEIGHT: 108.6pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl69 height=145 width=64>DAY</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 89pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl69 width=118>DATE</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=64>START TIME</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl71 width=64>round up to 8:00am or after 8:00am to nearest 15 mins</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl72 width=64>convert to decimal</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl70 width=64>FINISH</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl71 width=64>round down to nearest 15 mins</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 48pt; BORDER-TOP: windowtext 1pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl72 width=64>convert to decimal</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl73 height=19>MON </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 1pt solid" class=xl74>5/03/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75>7.55</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76>8.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>8.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75>4.56</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76>4.45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>4.75</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 height=19>TUE </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl74>6/03/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75>8.38</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76>8.45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>8.75</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75>4.39</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76>4.30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>4.50</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 height=19>WED </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl74>7/03/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75>7.34</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76>8.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>8.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75>5.10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76>5.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>5.00</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 height=19>THR</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl74>8/03/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75>8.43</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76>8.45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>8.75</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75>3.59</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76>3.45</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>3.75</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: white; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl78 height=19>FRI </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl74>9/03/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75>7.33</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76>8.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>8.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl75>6.37</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl76>6.30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl77>6.50</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d9d9d9; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl79 height=19>SAT </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl80>10/03/2012</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl81>0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl82> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl83> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl81>0.00</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl82> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl83> </TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BACKGROUND-COLOR: #d9d9d9; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl84 height=20>SUN </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 1pt solid" class=xl85>11/03/2012</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl86>0.00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl87> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl88> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl86>0.00</TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #f2dcdb; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl87> </TD><TD style="BORDER-BOTTOM: windowtext 1pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d9d9d9; BORDER-TOP: #f0f0f0; BORDER-RIGHT: windowtext 1pt solid" class=xl88> </TD></TR></TBODY></TABLE>

I really appreciate any guidance please....

Thank you
Narda

 

[repairman615]

Maybe something like:

Excel 2007
	B	C	D	E
8	7:48	8:00	4:36	4:30
Sheet1

Cell Formulas
Range	Formula
C8	=CEILING(B8,TIME(0,15,0))
E8	=IF(MINUTE(D8)<>MINUTE(CEILING(D8,TIME(0,15,0))),CEILING(D8,TIME(0,15,0))-TIME(0,15,0),CEILING(D8,TIME(0,15,0)))

 

[NKMS]

Hi Jeff

Thank you for the quick response.... I have been pulling my hair out all day !

I have implemented your suggestions and only have a couple of questions (if you don't mind)...

1. I think we just need to amend the first "ceiling" format to reflect the 8am cutoff please... I just tried and destroyed your good work any further suggestions much appreciated

and

2. to use these formulas successfully, it appears that the data entry has to be in the format of 8:12 - do you know of any way the data entry format could be 8.12 and still convert as per requirements (I just need to keep very simple for the end users).

Thank you again

 

[repairman615]

Excel 2007
	B	C	D	E
8	7:30	8:00	4:36	4:30
Sheet1

Cell Formulas
Range	Formula
C8	=IF(B88,0,0),TIME(8,0,0),CEILING(B8,TIME(0,15,0)))
E8	=IF(MINUTE(D8)<>MINUTE(CEILING(D8,TIME(0,15,0))),CEILING(D8,TIME(0,15,0))-TIME(0,15,0),CEILING(D8,TIME(0,15,0)))

I did notice you were using decimals in place of colons.

I am sure it is possible to write a formula for that, however I think it is better practice to enter time using the colon ).

Further, the formula would be more "buggy" to deal with as trying to decipher the values.

Just my opinion.

Will you be able to use the colon?

 

[NKMS]

Hi Jeff

Thanks... I would love to advocate the use of : as it makes sense however, I have to cater to the wider audience and I am only here on contract. However, you did give me food for thought - I am going to try reversing the formula order... ie convert to decimal first then attempt the rounding formulas.... fingers crossed.

Thanks again - have a great night

Cheers
Narda

 

[Biz]

Does this help? I have left variables in yellow shaded cells so that it easy to change formulas.

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Arial,Arial; FONT-SIZE: 8pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 63px"><COL style="WIDTH: 114px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"><COL style="WIDTH: 56px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD></TD><TD>A</TD><TD>B</TD><TD>C</TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">1</TD><TD>Actual Time</TD><TD>Contractors Rounding</TD><TD></TD><TD></TD><TD>Rounding</TD><TD>HR</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">2</TD><TD style="TEXT-ALIGN: right">8:20</TD><TD style="TEXT-ALIGN: right">8:15</TD><TD></TD><TD></TD><TD></TD><TD>Min</TD><TD style="TEXT-ALIGN: right; BACKGROUND-COLOR: #ffff00">15</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">3</TD><TD style="TEXT-ALIGN: right">8:25</TD><TD style="TEXT-ALIGN: right">8:30</TD><TD></TD><TD></TD><TD></TD><TD>Secs</TD><TD style="BACKGROUND-COLOR: #ffff00"></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD style="TEXT-ALIGN: right">8:30</TD><TD style="TEXT-ALIGN: right">8:30</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD style="TEXT-ALIGN: right">8:35</TD><TD style="TEXT-ALIGN: right">8:30</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD style="TEXT-ALIGN: right">8:40</TD><TD style="TEXT-ALIGN: right">8:45</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD style="TEXT-ALIGN: right">8:45</TD><TD style="TEXT-ALIGN: right">8:45</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD style="TEXT-ALIGN: right">8:50</TD><TD style="TEXT-ALIGN: right">8:45</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD style="TEXT-ALIGN: right">8:55</TD><TD style="TEXT-ALIGN: right">9:00</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD style="TEXT-ALIGN: right">9:00</TD><TD style="TEXT-ALIGN: right">9:00</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD style="TEXT-ALIGN: right">9:05</TD><TD style="TEXT-ALIGN: right">9:00</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD style="TEXT-ALIGN: right">9:10</TD><TD style="TEXT-ALIGN: right">9:15</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD style="TEXT-ALIGN: right">9:15</TD><TD style="TEXT-ALIGN: right">9:15</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD style="TEXT-ALIGN: right">9:20</TD><TD style="TEXT-ALIGN: right">9:15</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD style="TEXT-ALIGN: right">9:25</TD><TD style="TEXT-ALIGN: right">9:30</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">16</TD><TD style="TEXT-ALIGN: right">9:30</TD><TD style="TEXT-ALIGN: right">9:30</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">17</TD><TD style="TEXT-ALIGN: right">9:35</TD><TD style="TEXT-ALIGN: right">9:30</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">18</TD><TD style="TEXT-ALIGN: right">9:40</TD><TD style="TEXT-ALIGN: right">9:45</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">19</TD><TD style="TEXT-ALIGN: right">9:45</TD><TD style="TEXT-ALIGN: right">9:45</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">20</TD><TD style="TEXT-ALIGN: right">9:50</TD><TD style="TEXT-ALIGN: right">9:45</TD><TD></TD><TD></TD><TD></TD><TD></TD><TD></TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; BORDER-LEFT-STYLE: groove; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; FONT-SIZE: 10pt; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>B2</TD><TD>=ROUND(A2/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B3</TD><TD>=ROUND(A3/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B4</TD><TD>=ROUND(A4/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B5</TD><TD>=ROUND(A5/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B6</TD><TD>=ROUND(A6/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B7</TD><TD>=ROUND(A7/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B8</TD><TD>=ROUND(A8/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B9</TD><TD>=ROUND(A9/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B10</TD><TD>=ROUND(A10/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B11</TD><TD>=ROUND(A11/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B12</TD><TD>=ROUND(A12/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B13</TD><TD>=ROUND(A13/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B14</TD><TD>=ROUND(A14/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B15</TD><TD>=ROUND(A15/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B16</TD><TD>=ROUND(A16/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B17</TD><TD>=ROUND(A17/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B18</TD><TD>=ROUND(A18/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B19</TD><TD>=ROUND(A19/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR><TR><TD>B20</TD><TD>=ROUND(A20/TIME($G$1,$G$2,$G$3),0)*TIME($G$1,$G$2,$G$3)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Biz

 

[NKMS]

Hi Biz

Thank you for your response... I have sorted however appreciate your input.

Have a great night

Cheers
Narda