Excel question

Factual Questions
1

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

2

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

3
  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.
4

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!

5

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

6

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::

7

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