Time calculations, with a twist.

jag108

Active Member
Joined
May 14, 2002
Messages
420
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
Hi jag108:

Following is a formula based solution ... Let me know if I have understood you correctly ...
Book1
ABCDE
1
2StartTimeDurationNextStartTime
36:0023:59
415:0023:59
522:004:35
66:0023:59
7
Sheet6


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

this is then copied down!
 

jag108

Active Member
Joined
May 14, 2002
Messages
420
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

MrExcel MVP
Joined
Sep 9, 2004
Messages
5,432
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

Active Member
Joined
May 14, 2002
Messages
420
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

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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

Active Member
Joined
May 14, 2002
Messages
420
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
ABCD
101-12-0518:0000:30NewAmericanSportsman
201-12-0518:3000:30Fishing
301-12-0519:0000:30AroundtheHorn
401-12-0519:3000:30PardontheInterruption
501-12-0520:0001:00SportscenterLIVE
601-12-0521:0000:30ATPTennisWeekly
701-12-0521:3000:30PardontheInterruption
801-12-0522:0001:00IceSkating
901-12-0523:0002:30Golf
1001-12-0525:3000:30PardontheInterruption
1101-12-0526:0002:00Sportscenter
1201-12-0528:0001:30DragRacing
1301-12-0529:3000:30NFLFilmsPresents
1402-12-0506:0000:30NewAmericanSportsman
1502-12-0506:3000:30MotorsportAsia
1602-12-0507:0000:30DivingPassport
1702-12-0507:3000:30ATPTennisWeekly
1802-12-0508:0000:30ESPNFitnessPros
1902-12-0508:3000:30Anthology
DFU Template IBMS with formula
 

Yogi Anand

MrExcel MVP
Joined
Mar 12, 2002
Messages
11,454
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
ABCD
1starting values shown in light green
201-Dec-0518:000
3 0:30New American Sportsman
4 0:30Fishing
5 0:30Around the Horn
6 0:30Pardon the Interruption
7 1:00Sportscenter LIVE
8 0:30ATP Tennis Weekly
9 0:30Pardon the Interruption
10 1:00Ice Skating
11 2:30Golf
12 0:30Pardon the Interruption
13 2:00Sportscenter
14 1:30Drag Racing
15 0:30NFL Films Presents
16 0:30New American Sportsman
17 0:30Motorsport Asia
18 0:30Diving Passport
19 0:30ATP Tennis Weekly
20 0:30ESPN Fitness Pros
21 0:30Anthology
22
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

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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

Active Member
Joined
May 14, 2002
Messages
420
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?
 

Forum statistics

Threads
1,077,784
Messages
5,336,317
Members
399,074
Latest member
rlong98

Some videos you may like

This Week's Hot Topics

Top