compose time to quarters

A

Adora

Board Regular
Joined
Jun 21, 2014
Messages
50
Dears,

i need help in VBA as i have some IDs start & end shift times,
but i need to compose this shift time to intervals, eg: if ID: 4455 have a shift start 9:00:00 Am till 4:45:00 PM,
need these shift to compose to be 9:00:00, 9:15:00, 9:30:00 ,,,, till 16:45:00
or the Prtscr does make sense


0


i need to create the intervals as filled in column "B" based on 2nd shift time table, every ID have it's created intervals,

Gratefully,
 
Sort by date Sort by votes
You didn't say where your data was exactly, so assuming start time in C1, end time in D1.
Increment starts at B2. Change as necessary:

Code: 
Sub TEST()
Range("B2") = Range("C1")
With Range("B2:B" & Rows.Count)
    .DataSeries , step:=1 / 96, stop:=Range("D1")
    .NumberFormat = "h:mm AM/PM"
End With
End Sub
 
Upvote 0
sorry if the inputs aren't complete,
i need to fill the data at column "A" & "B" from the 2nd table as shown, the snap shot is for clarifying, i didn't have any data in "A" & "B", i need then to be as pictured

Gratefully,
 
Upvote 0
I can't see any sample, perhaps you could use this add-in: Download

or give a more detailed explanation of where your data is and where you want the time table built.
 
Upvote 0
Hello,

simply i have this table only, what i need is to compose every ID shift time to quarters, and this ID duplicate beside every quarter

[TABLE="class: outer_border, width: 300"]
<tbody>[TR]
[TD]ID[/TD]
[TD]Start shift[/TD]
[TD]End shift[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]3294[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9:00:00[/TD]
[TD]16:45:00[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]4554[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]9:00:00[/TD]
[TD]16:45:00[/TD]
[/TR]
[TR]
[TD]4556[/TD]
[TD]13:00:00[/TD]
[TD]20:45:00
[/TD]
[/TR]
[TR]
[TD]1783[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]13:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]20:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]4550[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]9:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]16:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]4553[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]13:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]20:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]2295[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]13:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]20:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]4547[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]9:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]16:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]4555[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]13:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]20:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]4551[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]13:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]20:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]3291[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]9:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]16:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]3122[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]9:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]16:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]4548[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]13:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]20:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]3514[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]13:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]20:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]4787[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]9:00:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]16:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]1782[/TD]
[TD]9:00:00[/TD]
[TD][TABLE="width: 84"]
<tbody>[TR]
[TD="class: xl66, width: 84"]16:45:00[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

as shown at the below picture, the table that located at left in column "A" & "B" didn't found this is for explanation, this table that i want to create via VBA.

0


the uploaded workbook,
http://1drv.ms/1DQjVVg
 
Upvote 0
OK, how about this?

Code: 
Sub test()
Dim c As Range
For Each c In Range("D2:D" & Range("D" & Rows.Count).End(xlUp).Row)
    Range("A" & Range("A" & Rows.Count).End(xlUp).Row + 1).Resize((c.Offset(, 2) - c.Offset(, 1)) / TimeSerial(0, 15, 0) + 1) = c
    With Range("B" & Range("B" & Rows.Count).End(xlUp).Row + 1)
        .Value = c.Offset(, 1)
        .DataSeries , rowcol:=xlColumns, step:=1 / 96, stop:=c.Offset(, 2)
    End With
Next
Range("B:B").NumberFormat = "HH:MM"
End Sub
 
Upvote 0
Hello Scott,

this is really what i looking for, thanks a lot for your co-operation

Gratefully,
 
Upvote 0
Hello Scott,

i have a trouble now with the table that have the IDs shift time, this data is extracted via VBA code based on cell color

Code: 
[COLOR=#333333][FONT=Segoe UI]Function strt(rng As Range) As String[/FONT][/COLOR][COLOR=#333333][FONT=Segoe UI]Dim c As Range
For Each c In rng
    If c.Interior.Color <> 16777215 Then
    strt = c.Column
    Exit Function
    End If
Next[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]End Function[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]Function nd(rng As Range) As String
Dim c As Range
Dim found As Boolean
For Each c In rng
If c.Interior.Color <> 16777215 Then found = True
    If found = True Then
    If c.Interior.Color = 16777215 Then
    nd = c.Column
    Exit Function
    End If
    End If
Next[/FONT][/COLOR]
[COLOR=#333333][FONT=Segoe UI]End Function[/FONT][/COLOR]

then when running your code have an error "Type mismatch" due to the source are hidden, it didn't appear unless double click in any cell, in addition to the ID column that your code is create filled with floating point, must use "text to column" option to remove floating, i need to remove floating to some calculation based on this ID columns,

i uploaded the entire workbook "your code" located at "Adherence" worksheet

http://1drv.ms/1zXSN2Q

Gratefully,
 
Upvote 0
What are you talking about? Time in Excel is stored as a fraction of a day. My code has a number format to show it as Time.
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Excel formula to allow display number of hours in particular periods of time
Replies
1
Views
191
myall_blues
myall_blues
G
  • Question Question
Help with formula time frame
Replies
3
Views
128
GenAkaman
G
C
  • Question Question
TIME LIMITATIONS
Replies
1
Views
238
cooleomter
C
D
  • Question Question
Can time frames/windows be used as a condition for any functions?
Replies
4
Views
296
dallas2699
D
V
  • Question Question
time and date fix
Replies
7
Views
189
vmjan02
V

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top