Time calculations, with a twist.

[jag108]

Hi,

I am trying to calculate the start time + duration to get next start time.

I have two columns column B has the start time, column "C" has the duration, I would like these to be added together to come up with the next start time.

This is a cross reference sheet to make sure that the data being imported is correct.

One thing I need to add is we work on a clock of 06:00 to 29:59 so a 30 hour clock, this is a broadcasting thing so don't get me started

My code looks like this.

Sub check_import()
Dim i As Integer, start_time, next_start_time, total_time As Date

On Error GoTo errortrap
vbyes_no = MsgBox("Please select the sheet you wish to run this code on. " & Chr(13) & _
"Is the correct sheet selected [Y/N]", vbInformation + vbYesNo, "Select the correct sheet.")
If vbyes_no = vbNo Then End
Set TestRange = Intersect(Range("E:E"), ActiveSheet.UsedRange)
lastrow = TestRange.Cells(TestRange.Cells.Count).Row 'get last row number in the range
firstrow = TestRange.Cells(1).Row 'get the first row number in the range
For i = 2 To lastrow Step 1
start_time = Cells(i, 2).Value
next_start_time = Cells(i + 1, 2).Value
duration = Cells(i, 3).Value
total_time = start_time + duration
If Not total_time = next_start_time Then
MsgBox "Cell " & Cells(i, 2).Address & " has the wrong duration."
End If
Next i
Exit Sub
errortrap:
MsgBox "An error has occured stopping this process form completing." & Chr(13) & _
"Error number: " & Err.Number & Chr(13) & "Error Description: " & Err.Description, vbExclamation + vbOKOnly, "An error has occured!"
End Sub

Any help really appreciated

 

[Yogi Anand]

Hi Jag108:

I don't understand your constraints ... so please post a screen shot of ...

1) your raw data before a macro is executed

2) your data after the macro is executed

3) how will the macro be executed?
a) through a command button
b) a worksheet event
c) ...

 

[jag108]

1. You have a screen shot of the data, that will not change, and cannot change. There can only be raw data because the import program does not like any thing else. This is why we hvae to make sure the data is correct before importing.

2. The data will only be changed by the user after the macro indicates that there is an issue with a particular event.

3. There will be a button on the standard excel toolbar, also created by VBA.

I will have the macro halt on the row where there is an issue, so that the user can investigate the problem. So it may need to be run multiple times to complete

 

[Yogi Anand]

Hi Jag108:

It appears that you only need to check that the StartTime entries are in your 30 Hour Recording cycle ... to automate it, you may want to run a macro on activating the sheet that ensures that each StartTime entry meets your desired criterion.

 

[jag108]

Yes my problem is getting the code that will do this.

All the variable combinations are treating the values as strings.

I need the cell values to be interpreted as either integers or some other numeric value.

I have tried a number of different methods but this is not happening.

 

[Yogi Anand]

Hi jag108:

Let me try again to see if the following will work for you ...

Let us say my data for StartupTime in cell B2 down is blank, is incorrect, or does not meet your 30 hour cycle ...

y051124h1.xls
	A	B	C	D
1	01-Dec-05	18:00	0:30	New American Sportsman
2	01-Dec-05	19:00	0:30	Fishing
3	01-Dec-05	20:00	0:30	Around the Horn
4	01-Dec-05	21:00	0:30	Pardon the Interruption
5	01-Dec-05		1:00	Sportscenter LIVE
6	01-Dec-05	23:00	0:30	ATP Tennis Weekly
7	01-Dec-05	24:00	0:30	Pardon the Interruption
8	01-Dec-05		1:00	0 Ice Skating
9	01-Dec-05		2:30	0 Golf
10	01-Dec-05		0:30	0 Pardon the Interruption
11	01-Dec-05		2:00	0 Sportscenter
12	01-Dec-05		1:30	0 Drag Racing
13	01-Dec-05	30:00	0:30	0 NFL Films Presents
14	02-Dec-05	31:00	0:30	New American Sportsman
15	02-Dec-05	32:00	0:30	Motorsport Asia
16	02-Dec-05		0:30	Diving Passport
17	02-Dec-05		0:30	ATP Tennis Weekly
18	02-Dec-05		0:30	ESPN Fitness Pros
19	02-Dec-05	36:00	0:30	Anthology
Sheet1

then after executing the following code ...

Private Sub CommandButton1_Click()
    For Each cell In Range("B2:B" & [a2].End(xlDown).Row)
        cell.Select
        cell.Value = cell(0, 1) + cell(0, 2)
        Debug.Print cell.Value * 24
        cell.Value = (cell.Value * 1440 Mod 1800) / 1440
        Debug.Print cell.Value
        If cell.Value< 0.25 Then cell.Value = cell.Value + 0.25
    Next cell
End Sub

my data in cells B2 down is made to comply with your requirements ...

y051124h1.xls
	A	B	C	D
1	01-Dec-05	18:00	0:30	New American Sportsman
2	01-Dec-05	18:30	0:30	Fishing
3	01-Dec-05	19:00	0:30	Around the Horn
4	01-Dec-05	19:30	0:30	Pardon the Interruption
5	01-Dec-05	20:00	1:00	Sportscenter LIVE
6	01-Dec-05	21:00	0:30	ATP Tennis Weekly
7	01-Dec-05	21:30	0:30	Pardon the Interruption
8	01-Dec-05	22:00	1:00	0 Ice Skating
9	01-Dec-05	23:00	2:30	0 Golf
10	01-Dec-05	25:30	0:30	0 Pardon the Interruption
11	01-Dec-05	26:00	2:00	0 Sportscenter
12	01-Dec-05	28:00	1:30	0 Drag Racing
13	01-Dec-05	29:30	0:30	0 NFL Films Presents
14	02-Dec-05	06:00	0:30	New American Sportsman
15	02-Dec-05	06:30	0:30	Motorsport Asia
16	02-Dec-05	07:00	0:30	Diving Passport
17	02-Dec-05	07:30	0:30	ATP Tennis Weekly
18	02-Dec-05	08:00	0:30	ESPN Fitness Pros
19	02-Dec-05	08:30	0:30	Anthology
Sheet1

I hope this helps!