I have a file with data from a cantiliver beam experiment. The problem is I am not able to use the time format which is in the file. I want to make a plot for the two values in coloum D and E against time (coloum C). Where the time goes from 0 sec at the first data point and to xx.xxx secounds at each row.
You have too many colons ( : ). The Time format needs to be hh:mm:ss.000. The numbers are just seen as Text.
In cell F3, next to your Table, put the formula:and format the cells in the column as "hh:mm:ss.000". Column F will be added to your Table. If you wish, you could copy and Paste Special Values and Formats over the original Column3.Formula:
Please Login or Register to view this content.
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
See the attached updated example.
Personally, I'd remove the blank row 2 and all the blank rows in your table below the data. They don't add any value. Structured Tables automatically propagate formulae and formatting.
Note that the "dates" in Column2 are not true Dates and the "numbers" in Column4 and Column5 are not true numbers.
See the attached example.
Hi there,
Just a further comment.
Looking at your data, is it likely that the hour value will change throughout the course of a series of readings? If not, you can use the following formula (in cell F4) to convert your time values to seconds:
Please Login or Register to view this content.
If neither the hour nor the minute value is likely to change, you can use the following formula in Cell F4:
Please Login or Register to view this content.
This approach will keep the data values at a more "manageable" level.
Regards,
Greg M
I checked out your solution TMS, but when I did this the time value has changed to an incorect time it seems.
The time is simply the time of day the tests was conducted. therefore the hour might change if future tests are conducted just as a new hour is starting.
Sorry, I copied the formula from one Table to the other and it added the original Table Name. The formula should just be:Formula:Please Login or Register to view this content.
See attached.
Probably my skills is the problem here, but when I entered the new formula text the cell just goes blank with no value.
Just to clearify, I attached a picture of what I want to get from the time data (I wrote the numbers manually as text, just for the picture)
See the attached.
Seems one of the problems also was a problem with my defult unit settings. Your v4 works perfectly! thank you!
See attached
I found another problem. When the time is 11:15:45:001 the data is written as 11:15:46:1 which then is interpeted as 11:15:45:100. Is there a way of solving this?
Thank you so much for all your help!
I don't see that:
11:15:45:001 11:15:45.001
See picture.
Thank you!
Ah, now I understand. Who or what writes/provides this data? Ideally, it needs to be corrected at source. As it stands, I think you'd need to check the length of the field and pad with one or two zeroes before substituting the point to convert it to time. Probably doable but I don't have time right now. It would be difficult to do if any of the other sections were not fixed at two digits.When the time is 11:15:45:001 the data is written as 11:15:46:1 which then is interpeted as 11:15:45:100.
OK, I think this resolves the latest issue:
Formula:Please Login or Register to view this content.
See attached.
I guess the problem here is the number of variations, since both the hour minute and second, could be written as 1 or 2 digits.
Like I said, fix it at source. Either that, or split hours, minutes, seconds and milliseconds using the colon as a separator, format them individually, and collate in timeformat.
I'm afraid I can't keep working on this, catering for each variation as it turns up. You started with "nn:nn:nn:nnn", moved to "nn:nn:nn:??n", and now it can be "?n:?n:?n:?nn"
For simplicity, the easiest way would be to add four helper columns, use a formula or Text to Columns to split the input cells, and build the time value from scratch.
Thank you for your help! Came alot further with it. Will try to solve this my self now.
Cheers!
Thank you for your help! Came alot further with it. Will try to solve this my self now.
Cheers!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks