Counting Minutes

A

AlEvanPA

I am new to VBA. I have an Excel Spreadsheet where each row is seperated by
an hour as shown below:
Date Time Col1 Col2
1/1/06 22:00 Val1 Val2
1/1/06 23:00 Val3 Val4
1/2/06 00:00 Val5 Val6

What I want to happen is to run a Macro (confugred with VBA) that does the
following to the input data:

Date Time Col1 Col2
1/1/06 22:00 Val1 Val2
1/1/06 22:01 Val1 Val2
1/1/06 22:02 Val1 Val2
....
1/1/06 22:59 Val1 Val2
1/1/06 23:00 Val3 Val4
1/1/06 23:01 Val3 Val4
....
1/1/06 23:59 Val3 Val4
1/2/06 00:00 Val5 Val6
1/2/06 00:01 Val5 Val6
....
1/2/06 00:59 Val5 Val6
, etc.

I am able to get the date and value columns to copy correctly, but have no
clue how get the time (minute) to sum.

Can someone please help.

Thank, In advance
 
K

Kevin B

You can get your time values with a formula. The following example assumes
that the first time value is in cell A1:
A
1 22:00
2 =TIME(HOUR(A1),MINUTE(A1)+1,0)

Then copy the formula down the column.
 
S

Sandy

If you want to use a macro you can try this:

Sub DoTime()
'Assuming that Range("B1") is your starting time
'and entered into the spreadsheet
Dim MyRow As Object
On Error Resume Next
For Each MyRow In Range("B2:B100")
'or what ever range you'd like
MyRow.Value = Format(DateAdd _
("n", 1, MyRow.Offset(-1, 0).Value), "hh:nn")
Next
End Sub

Sandy
 
A

AlEvanPA

Kevin,
Thanks. This was helpful to me in working directly in the spreadsheet.
Al Evangelista
 
A

AlEvanPA

Sandy,
Thank you. I was able to get this running.
I had some difficulty, however, once I worked out the bugs,
it ran great.

Sorry it took me so long to get back to you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top