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:

Following is a formula based solution ... Let me know if I have understood you correctly ...

Book1
	A	B	C	D	E
1
2		StartTime	Duration	NextStartTime
3		6:00	23:59	29:59
4		15:00	23:59	8:59
5		22:00	4:35	26:35
6		6:00	23:59	29:59
7
Sheet6

formula in cell D3 is ... =MOD(B3+C3,"30:00"+0)

this is then copied down!

 

[jag108]

You have understood exactly the thing I am trying to do.

When I tried using your formula I get an error.

"You've entered to few arguments for this function."

This is what I have entered
=mod(B2+C2), "30:00" +0)
I am using Excel 2003, if that makes a difference.

But ultimately I would like this done by VBA some way.

 

[Andrew Fergus]

Hi
I think you have an extra closing bracket in your formula. Have a look at your formula compared to that from Yogi Anand.
Andrew

 

[jag108]

ADVERTISEMENT

Well spotted, cheers andrew93.

That works, but I still would really really like this done with VBA, I do not have the spare columns to work with.
The file will be imported into another App so it is looking for specific columns, if there are more columns than ii si looking for it will spit the dummy.

 

[Yogi Anand]

Hi jag108:

I don't see any problem converting the logic of the formula in use with VBA ... however

how do you want to set it up?

where would the StartTime be noted?

where would the Duration be noted?

and where would the computed NextStartTime be noted?

It will be nice to see what is your setup and how your data is displayed ... please consider posting an HTML screenshot of your sample data.

 

[jag108]

ADVERTISEMENT

Hi Yogi,

I need to verify the start time to the duration so that we do not have an overlap of events, this is critical for the application that is importing the data. The data is laid out col A = Date, col B = start time, col C = duration.

Import_checker5.xls
	A	B	C	D
1	01-12-05	18:00	00:30	NewAmericanSportsman
2	01-12-05	18:30	00:30	Fishing
3	01-12-05	19:00	00:30	AroundtheHorn
4	01-12-05	19:30	00:30	PardontheInterruption
5	01-12-05	20:00	01:00	SportscenterLIVE
6	01-12-05	21:00	00:30	ATPTennisWeekly
7	01-12-05	21:30	00:30	PardontheInterruption
8	01-12-05	22:00	01:00	IceSkating
9	01-12-05	23:00	02:30	Golf
10	01-12-05	25:30	00:30	PardontheInterruption
11	01-12-05	26:00	02:00	Sportscenter
12	01-12-05	28:00	01:30	DragRacing
13	01-12-05	29:30	00:30	NFLFilmsPresents
14	02-12-05	06:00	00:30	NewAmericanSportsman
15	02-12-05	06:30	00:30	MotorsportAsia
16	02-12-05	07:00	00:30	DivingPassport
17	02-12-05	07:30	00:30	ATPTennisWeekly
18	02-12-05	08:00	00:30	ESPNFitnessPros
19	02-12-05	08:30	00:30	Anthology
DFU Template IBMS with formula

 

[Yogi Anand]

Hi jag108:

I have made a little modification to your layout (hopefully that will not cause any problems) ... well first let us look at the proposed solution ...

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

formula in cell A3 is ... =INT($A2)+($B2+$C2)/("30:00")

and formula in cell B3 is ... =MOD(B2+C2,"30:00"+0)

these are then copied down

 

[daniels012]

I do something simular. This make work for your situation.

I have times listed. Then we have breaks in 30 minute increments.

A B
12:00 AM 30
12:30 AM 30
1:00 AM 60
2:00 AM 120
4:00 AM 60
5:00 AM 30
5:30 AM 30
6:00 AM 120
8:00 AM

My formula starting in Cell A2 (The Bold Cell) is:

=A1+(B1/1440)

This adds whatever is entered in column B in the last row.


Hope this helps,
Michael

 

[jag108]

Hi Yogi,

You understand my issue, but I cannot use the formula you are proposing, for the sheer sake it does not do what I require. I cannot change the layout of the file ata all due to it is what the import program is expecting, if anything is out of sequence it will fail. The code I posted ealrier in the post is what I need to do.
becuse the spread sheet will be simply just data I will have the code in another sheet that will prompt the user to open the data sheet, so tha it can run over the start time calculate it to the duration then compare this to the next start time, if there is an error then prompt the user that there is an issue with that particular event. So the formula idea may work but is not usable in my situation, sorry!

If that could be turned into simple VBA then how?