Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Minutes
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Minutes
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. -- Kevin Backmann "AlEvanPA" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Counting Minutes
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 AlEvanPA wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert text entered as minutes/seconds to minutes | Excel Worksheet Functions | |||
Converting total minutes into hours and minutes in Excel | Excel Worksheet Functions | |||
converting Days Hours & minutes into just minutes in excel | Excel Discussion (Misc queries) | |||
Counting of certain hrs in to minutes | New Users to Excel | |||
add column of minutes, show total in hours & minutes | Excel Worksheet Functions |