# converting times ANOTHER Excel question

**URL:** <https://boards.straightdope.com/t/converting-times-another-excel-question/477735>\
**Category:** Factual Questions\
**Created:** [December 18, 2008, 1:15pm UTC](https://boards.straightdope.com/t/converting-times-another-excel-question/477735 "2008-12-18T13:15:49Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Sigene](https://avatars.discourse-cdn.com/v4/letter/s/df788c/32.png) [@Sigene](https://boards.straightdope.com/u/Sigene)\
**Post date:** [December 18, 2008, 1:15pm UTC](https://boards.straightdope.com/t/converting-times-another-excel-question/477735/1 "2008-12-18T13:15:49Z")

</div>

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:

11DEC2008 2008-12-11-19-57-23-GMT.abc --------0  
11DEC2008 2008-12-11-19-57-51-GMT.abc --------28  
11DEC2008 2008-12-11-19-58-20-GMT.abc --------57

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?

---

<div class="post-metadata">

**Author:** ![chromaticity](https://avatars.discourse-cdn.com/v4/letter/c/cc9497/32.png) [@chromaticity](https://boards.straightdope.com/u/chromaticity)\
**Post date:** [December 18, 2008, 2:06pm UTC](https://boards.straightdope.com/t/converting-times-another-excel-question/477735/2 "2008-12-18T14:06:36Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![K364](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/k364/32/5_2.png) [@K364](https://boards.straightdope.com/u/K364)\
**Post date:** [December 18, 2008, 2:44pm UTC](https://boards.straightdope.com/t/converting-times-another-excel-question/477735/3 "2008-12-18T14:44:22Z")

</div>

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 60_60_24=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)