I have data with file names with a time stamp in them. I want to convert these file names so that I can easily graph the data using time of reaction as the independant variable (x axis).
For example heres a couple of file names and what I want
File name ----------------------------------What I want (time in seconds)
XXXX 12-11-2008 12:55:28 -----------------0
XXXX 12-11-2008 12:55:56 -----------------28
XXXX 12-11-2008 12:56:25 -----------------57 etc.
I also have file names like this:
How can I easily convert either or both of these names to actual times. Note, they aren’t consistently 28 seconds apart, so I just can’t go in and add 28 seconds to each subsequent entry?
You can use Text to columns to separate the fields and then use normal excel functions to subtract. Handling hours and minutes separately is better, I think…
XXXX 12-11-2008 12:55:56
XXXX 12-11-2008 12:56:25
Becomes (using : and space as the separators)
XXXX |12|55|56 -> 0|0|0
XXXX |12|56|25 -> 0|0|29 (i know, i know i am drunk now and cant think about compensating for the minutes)
Excel stores dates as numbers with the days as whole integers and the remaining hours/secs as part of the decimal fraction. So, today at midnight is 39800, and 39800.1 is 1/10 of day after.
So, subtract the two dates and multiply by 86400 to convert the fraction to seconds. Because there are 606024=86400 seconds in a day.
p.s. = the cell entry has to recognized as a date. Some things that look like dates are just text because they weren’t entered according to the date format rules as set on your computer (Control Panel/Regional Settings)