# Excel question

In the table below, how do I calculate the # of elapsed days between columns A and B?

20080110 20081125
20080204 20081210
20080425 20081125
20080612 20081023
20080703 20090218
20080721 20081212
20080730 20081209
20080731 20081125
20080805 20081115
20080808 20081024
20080822 20081117
20080822 20081110
20080828 20081120
20080829 20081024

Column C is this:
=MID(A1,5,2) & “/” & MID(A1,7,2) & “/” & MID(A1,1,4)
Column D is this:
=MID(B1,5,2) & “/” & MID(B1,7,2) & “/” & MID(B1,1,4)
and then Column E is simply:
=D1-C1

1. Change both columns to be “General” Format.
2. Highlight the first column. On the toolbar, click on Data -> Text to columns -> Next -> Next -> In the upper right hand column set the Column Data Format to “Date YMD”. Do this for both columns.
3. In Column C, set it to be B1 - A1.

Wow! I’ve seen that dialog before, but only when importing from a text file. I didn’t know it works on stuff that’s already typed in. Way to go! Thanks!

After doing that, you can use the undocumented DATEDIF function to get, say, the number of months between the dates. Set C = DATEDIF(A1, B1, “d”) for days, “m” for months etc.

http://www.cpearson.com/excel/datedif.aspx

Wow. And i thought I was smart.

Too technical for me. I was going to suggest changing the field to ‘date’ format and then sort by that.

::bowing out to the kids table::

Excellent thanks! I didn’t know this either and it will come in very handy.