#### darkbearpooh1

##### New Member
ok here is the whole vba page

Code:
``````Function GetTime(rng As Range, ci As Long, Start As Boolean)
Dim cell As Range
Dim i As Long
Dim j As Long
If Start Then
For i = 1 To rng.Columns.Count
If rng.Columns(i).Interior.ColorIndex = ci Then
j = IIf(i > 25, i - 24, i)
GetTime = TimeSerial(Int(j / 2), (j Mod 2) * 30, 0)
Exit For
End If
Next i
Else
For i = rng.Columns.Count To 1 Step -1
If rng.Columns(i).Interior.ColorIndex = ci Then
j = IIf(i > 25, i - 24, i)
GetTime = TimeSerial(Int(j / 2), (j Mod 2) * 30, 0)
Exit For
End If
Next i
End If
End Function``````

ok this works perfect for me as i needed it before it essentially counts the colored cell giving it a time value it counts one cell as say 7am and if the second is colored it counts as 7:30 and the third as 8am for the start time, but now i need to modify the whole thing to count by 15 minutes instead of 30 minute intervals i have changed this part GetTime = TimeSerial(Int(j / 2), (j Mod 2) * 30, 0) to GetTime = TimeSerial(Int(j / 2), (j Mod 2) * 15, 0) and it does count the first colored block as 15 minutes but then the second and third should go up to 30 minutes then 45 then fourth to 1 hour increments but that only changes the first block to 15 minute and then the second goes to a round hour but it should be four blocks of 15 minutes to make the hour first

Last edited by a moderator:

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Any ideas

#### AlphaFrog

##### MrExcel MVP
Try this...
Code:
``GetTime = TimeSerial(Int(j / [COLOR="Red"]4[/COLOR]), (j Mod [COLOR="Red"]4[/COLOR]) * [COLOR="Red"]15[/COLOR], 0)``

Replies
2
Views
443
Replies
1
Views
396
Replies
3
Views
226
Replies
15
Views
552
Replies
0
Views
455

1,190,768
Messages
5,982,824
Members
439,799
Latest member
matts12

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back