Adjusting time - 12hr to 24hr

Hello,

This is my first time on such a forum but I am in real need of help! I currently have a huge amount of Data recorded by our company website that I need to adjust before I can begin comprehensive data analysis. The main issue I need help with is:

1. The website automatically generates a time when an item off our site is downloaded but the development team (no longer at the company) programmed it in such a way that it gives the time in 12hr clock but we need to know it in 24hr time for the analysis. This is not a simple case of changing the formatting.

eg:

Downloaded Date Downloaded Time
2012-03-29 09:43:36

I can tell by the chronology what items are "am" and what are "pm", but I am unable to go through and manually add 12 hours to 40,000 items. Idealls I would just be able to convert eat time into a number like 09:43:36 would be "9" and then I could do a simple formula to add 12 hours to all pm entries?

Sorry if this is unclear, I would really appreciate some help on how to

Other details:

Using excel 2010
This spreadsheet is massive
Please go easy on me

Hi asimbig,

welcome to the forum. Some questions:
1. Are the cells recognized as date by excel? Or are they text?
2. How do you know which time is supposed to be AM and which is PM? Is there an algorhythm that can be applied?

Originally Posted by RHCPgergo

Hi asimbig,

welcome to the forum. Some questions:


1. Are the cells recognized as date by excel? Or are they text?
2. How do you know which time is supposed to be AM and which is PM? Is there an algorhythm that can be applied?

1. I assume they are recognised as time - although another problem is they are left aligned and I need to double-click into the cell to make it right aligned (and also have any formulas work). See attached file

2. With each entry comes an ID number - because downloads are high and I can see the date in another column I can tell what order the events happened. Have a look at the sheet. asimbig sample.xlsx

All help is really appreciated!

Yeah, the cells are recognized as text. Luckily, there's a simple solution for that. Try text to columns and choose date for the column data format. Here's some guidance if you don't manage on your own.
http://excelhints.com/2010/01/29/usi...separate-data/

That is unbelievably helpful, thank you!