Dates not formatted as dates

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?

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

Pete

Here is a sample.

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

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.

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:

Formula:

Please Login or Register  to view this content.

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

Formula:

Please Login or Register  to view this content.

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

Thank you!

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

Pete