Adding time interval to time

[pooj_malh]

Hi

I have a start time and then subsequent time intervals for each sample of reading that I am taking. I want to add the absolute start time to my time interval to have absoute time with each reading.

Also i need to make macro out of it so that each time user opens file he gets all the calculations done.

Any help would be greatly appreciated.

Thanks and Regards
Pooja

 

[pooj_malh]

Hey Eric,
the formula you gave for time worked beautifully. I guess I have a problem running the macros(The code for VBE that you gave ..I cud not test it...security issue).Nevermind !!!!

Now I want to create a template of the whole thing and apply this formula to all the time interval cells.

Can you suggest something?

Thank you so much for the previous help.

Thanks and regards
Pooja

 

[erik.van.geit]

Now I want to create a template of the whole thing and apply this formula to all the time interval cells.

can you provide an example of what you want to do exactly ?

 

[pooj_malh]

Channels 1
Samples 100
Date 9/15/2006
Time 00:28.4
X_Dimension Time
X0 0.00E+00
Delta_X 0.001
***End_of_Header***
X_Value Sine (Collected) Comment
0 0
0.001 ,0.064672, 13:00:28.351
0.002 ,0.129073 , 13:00:28.352
0.003 ,0.192933 ,13:00:28.353
I have added commas to signify different columns


This is a typical data file that i want to create. The last column is the start time + time interval for each sample, which I have created manually now.
I want to create a template so that when the raw data is fed to the sheet, the 3rd column is built automatically.

I hope that makes any sense. Let me know if you need more information

Thanks again
pooja

 

[erik.van.geit]

column B is not relevant or is it ?

Time 00:28.4
shouldn't that be
13:00:28.350

or do you want to put the starttime somewhere else ?
for this code it is assumed that the starttime resides in cell C2

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
'put value in column C, not as a formula but only the value itself
Target.Offset(0, 2) = Range("C2") + Target
'to put formula in worksheet use next line
'then delete quote in next line and add quote before previous line
'Target.Offset(0, 2) = "=C2+" & Target.Address(0, 0)
End Sub

TO INSTALL IN SHEET CODE WINDOW:
1. right click the "Name Tab" of sheet you want code to work in
2. Select "View Code" in drop down menu
3. VBE window will open ... paste code in and exit VBE

 

[pooj_malh]

Hey Eric,

yes column B is relevant it is the actual data(samples) taken at different time intervals.
I am pasting the raw data file again

The start time will be somewhere else.

I hope this helps you understand the problem.

Your solution I am trying after modifying it a bit.

LabVIEW Measurement
Writer_Version 0.92
Reader_Version 1
Separator Tab
Multi_Headings Yes
X_Columns Multi
Time_Pref Absolute
Operator Lisa
Date 9/15/2006
Time 37:14.6
***End_of_Header***

Channels 1
Samples 100
Date 9/15/2006
Time 37:14.6
X_Dimension Time
X0 0.00E+00
Delta_X 0.001
***End_of_Header***
X_Value Sine (Collected) Comment
0 0
0.001 0.064672
0.002 0.129073
0.003 0.192933
0.004 0.255986
0.005 0.317967
0.006 0.378616
0.007 0.437681
0.008 0.494913
0.009 0.550073
0.01 0.60293
0.011 0.653262
0.012 0.700859
0.013 0.745523
0.014 0.787064
0.015 0.825311
0.016 0.860102
0.017 0.891292
0.018 0.91875
0.019 0.942362
0.02 0.962028
0.021 0.977666
0.022 0.989211
0.023 0.996614


Now see this is the complete data file(raw)

 

[erik.van.geit]

yes column B is relevant it is the actual data(samples) taken at different time intervals.

but it is not relevant for the timecalculation, is it ?
that was the meaning of my question: not if it is relevant for you, but for the problem here on the Board
if it is relevant for this problem, then I'm still missing something

The start time will be somewhere else.

you can take care of the code to change to the starttime-cell

I think you have recieved everything you need.
1. experiment with the code on an empty sheet: you will see what happens
2. write a starttime somewhere, adapt your code accordingly (correct address for starttime) and try again
3. check out how you can make it work for your project

almost sleeping,
Erik

 

[pooj_malh]

Thanks a lot Eric,

Good night. I ll try out this code and get back to you tomorrow.

Thanks and regards
Pooja

 

[pooj_malh]

Hey,

Thanks a lot for the help. I could finally create a macro that gave me the right answer.

This was indeed a great help.

I had the thing working yesterday only but I wanted to see the results differently.

Thanks Man

 

[pooj_malh]

Hey Eric,

Now I have been into another trouble. I want to autofill rest of the cells in that column. When I save the macro after autofilling it , next time I am trying to run the macro(on a new data file) it is autofilling the data only till the last column when I save the file.
This is my code

I hope this is making any sense.

Can you help me putting an autofilling thing into the macro?

Thanks for your support.

Regards
Pooja

Sub Time_macro()
'
' Time_macro Macro
' Macro recorded 9/27/2006 by Herbst
'

'
    Range("C22").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC1+R16C2/86400"
    Range("C22").Select
    Selection.NumberFormat = "h:mm:ss.000 AM/PM"
    Range("C22").Select
    Selection.ClearContents
    ActiveCell.FormulaR1C1 = "=R16C2+RC1/86400"
    Range("C22").Select
    Selection.AutoFill Destination:=Range("C22:C521")
    Range("C22:C521").Select
End Sub

[/code]

 

[erik.van.geit]

your question is not clear to me
especially this part

it is autofilling the data only till the last column when I save the file

I do not see the relation between save and runnning a macro

by the way
I think you can replace your code by one line

    Range("C22:C521") = "=RC1+R16C2/86400"