Dates not formatted as dates

[Daisy22]

Hi,

I am working with an excel file I imported from online with lots of data. I am trying to arrange the data by date. Unfortunately all of the data in the file (both numbers and dates) are reading as text. I tried to right click and change formatting, but that had no effect. I tried using text to columns, but also did not have success. Any suggestions or am I ordering this data by hand?

[Pete_UK]

Post a sample file so that we can see the variations. Presumably your preferred format is mm/dd/yyyy ?

Pete

[Daisy22]

Here is a sample.

[Pete_UK]

Can you tell me what MST means and how that relates to you own time-zone. Do you want dates and times in separate columns for this?

Pete

[Daisy22]

MST is Mountain Standard Time and MDT is Mountain Daylight time. These data were collected from a SNOTEL site in Glacier National Park, US. I am not sure why it changes, but probably has something to do with ease of recording. I do not mind if times are with dates, I just need them to register as such with excel. It may help me solve some of my problems, however, if they were separated. Whichever is easiest.

Thank you for taking a look at this.

[Pete_UK]

I found it more convenient to insert two new columns A and B, so the other data shifts over. Then you can put this formula in A2 to get the date in Excel format:

=DATE(RIGHT(LEFT(C2,SEARCH(" ",C2)-1),4),LEFT(C2,SEARCH("-",C2)-1),SUBSTITUTE(MID(C2,SEARCH("-",C2)+1,2),"-",""))

and this formula in B2 extracts the time in Excel format:

=--SUBSTITUTE(SUBSTITUTE(RIGHT(C2,LEN(C2)-SEARCH(" ",C2))," MST",":00")," MDT",":00")

After formatting how you would like them to appear, you can then copy them down to the end of your data. If you want the date/time combined, then all you need to do is:

=A2+B2

and format appropriately, then copy down.

Hope this helps.

Pete

[Daisy22]

Thank you!

[Pete_UK]

You're welcome - I didn't expect you to be logged in at this time.

Pete