False to True Transition Calculation

[Sparky2000]

Good Day All,
I am new to this Forum and this is my First ever post.
I am trying to find out if I could calculate the time differences between 0 to 1 Transitions in a Column.Sample of the File is attached.
A file such as the one in the attachment is created at midnight everyday.This happens on one of the computers for Data Acquisition on a machine.
I have been asked to calculate the time intervals every time a transition from a 0 to a 1 (Blue Arrows) happens. This file is updated every three seconds (red arrow) as shown in the attachment.The times calculated can be stored in Column D.(where the Blue Arrows are located in the file).
Just before midnight, I would like to save this file (new file name) with the date, the rows with Transitions only and the calculated Time Intervals.This file has to be emailed to a group of users in our factory.
Right after midnight, a new file is created and overwrites the original file.
Is this possible to do using Excel VBA?
Any help or suggestions will be appreciated.
Thank you all in advance.

[Crooza]

changeofstate.xlsx

Just to get you going this will calculate the times between the change of state.

[patel45]

Please Login or Register  to view this content.

copy column B format in column E

[Sparky2000]

Good Day Crooza and patel45,
Thank you very much for your feedback.Both solutions worked.
I would also like to find out how can the following be done:-
a)If I wanted to find out when the each change of state occurred with reference to mid-night (from the time the file was created) in a separate column.
b)How many times the change of state occurred during the whole day.
c)Save this file just prior to midnight with a unique file name with date.
d)Email the saved file to a number of people.
Thanks in advance.

[Crooza]

for a) isn't this what you've got in column B
for b) put this in F1 in my example above

Please Login or Register  to view this content.

for c) I suspect there's a batch file routine you can run to do this but I've never done this. Some fo the smarter guys and gals can do this
for d) If you do a search on here you'll find plenty of examples of vba code to send files through outlook.

[Sparky2000]

Hello Crooza,
Thank you very much for all the help.
Take care and have a great day!

[Sparky2000]

Hello All,
How can I calculate the average time of all 0 to 1 transitions?
Thanks in advance.

[Crooza]

Have you tried using the average formula? Look at where I put the countif formula and use the same range as this will average the transition times

It should look like =average(range)

[Sparky2000]

Crooza,
It works! Thanks a lot once again.

[Crooza]

I'm glad it worked. In regard to your automatic email issue the following code may assist although I'm not sure how you automate the run time.

It's a bit like a mailmerge for email. You could simplify it by hardcoding the email addresses and other fields in but this one also gives you the flexibility of sending multiple emails to people and slightly adjusting the email like a mail merge so it's a little more personal.

As well as the code I've attached the workbook with the code in so you can see how I do this.

Please Login or Register  to view this content.

mail merge excel macro v1.xlsm

[Sparky2000]

Crooza,
Thanks for your feedback. I will try it as soon as I have a few minutes.
I was looking at the file that I have to work on.I have to calculate all the Zero to One Transition times.Once that is done, I have to create a chart for the Time mentioned in Column "B" (X-Axis) and Zero to One Transition Times (Y-Axis). Once the chart is done, I have to calculate the Zero to One Transitions that are a)equal to 10 b)Greater than 10. These values (a) and (b) can be stored in two individual cells.
Once the above is done, I have to email the spreadsheet to numerous people.
The file is attached.
Thanks in advance and have a great day!

[Crooza]

Sorry for delay,

I've been on other things.

If you put this in d3 and copy down

Please Login or Register  to view this content.

and this in E3 and copy down

Please Login or Register  to view this content.

this

Please Login or Register  to view this content.

, this

Please Login or Register  to view this content.

and this

Please Login or Register  to view this content.

in three separate cells you'll get the average, less than 10 and greater than 10 counts.

I don't know what you want to graph though. If you have a time series along the X axis representing the whole day do you then want to show columns when ther transition occurs and the column hieght is the time to transition?

[Sparky2000]

Crooza,
Thanks for the update.
I would like to graph the "Actual Time" when the 0 to 1 Transition took place on x-axis versus "Time Between Transitions" on the y-axis.
Have a great day!

[Crooza]

0 to 1 transition short version.xlsx

Sparky,

Sorry for the delay.

The attached will work but I had to cut it down in size to get it to meet the server limits for file size.This is a real pain by the way.

You can add any day's data in to columns A- C and the spreadsheet will do teh rest in regards to your average, <10 and >10 minutes calcs and the graphing. It basically grabs justthe data that is relevant for graphing from teh big stream of data in columns d and E then graphs that. The graph will look a bit weird until you put your data back in and then extend the formulas in columns D and E. Essentially I cut the bottom few thousand rows off to meet the file size.

If this doesn't work let me know and if you can give me an email address I can email it to you direct. The file in its full form is only just over 1 MB

[Sparky2000]

Good Crooza,
Thanks for the update. I will check it out and let you know how it pans out.
Have a great weekend!