Date with Military time

[Mr_Scott]

Greeting all,
Need some help, I currently have a date and time in Column A. and the same in B (i.e 8/30/11 13:25) and I have it subtract B from A as some of these times can run past a single day, this total pops up in my C column in hours of time only.
What I need to do is remove the colon from the military time, during entry (i.e. 08/30/11 1325) As I have about 124,000 time and dates to enter into a spread sheet. I have seen that it can be removed, but can't find any coloration of doing it connected to a date... Any ideas?

 

[Steve=True]

Hi Mr Scott,

Welcome to the Forum!

I am not sure why you want to strip it out.

When i enter 08/30/2011 13:25 in my 2007 spreadsheet, i see the following in my formula bar 8/30/2011 1:25:00 PM

Since this is being treated as a date, you can format it to look like you want, but it is not truly removing the ":"

Highlight your column of data and apply this custom format:

m/d/yy hmm;@

It started out like this and i removed the ":" m/d/yy h:mm;@ but that is not really removing the ":" it is just formatting. Kinda like there is not really a "$" in front of currency, it is only diplayed via formatting.



Steve=True
www.exceldashboardtemplate.com

 

[Jonmo1]

As long as your entry remains consistent...
mm/dd/yy hhmm

Then you can use a formula like this in an adescent cell

=TEXT(RIGHT(A3,4),"00\:00")+DATEVALUE(LEFT(A3,8))

 

[Mr_Scott]

I am doing this because I am using a separate key pad for the data entry, and I can use a slash or a hyphen when putting in the date, but its extremely time consuming to then go back to keyboard and then press shift+; to get the colon.... and am serious when I have more or less 124,000 of these entries to make...

 

[Jonmo1]

I am doing this because I am using a separate key pad for the data entry, and I can use a slash or a hyphen when putting in the date, but its extremely time consuming to then go back to keyboard and then press shift+; to get the colon.... and am serious when I have more or less 124,000 of these entries to make...

I assume you're reading it from somewhere..
Can it be copy pasted into Excel?

 

[Steve=True]

Makes more sense. You may want to see if you can scan the document and have the computer do it and then check/review for errors

 

[Mr_Scott]

Yes, after a few manipulations of the C column, it worked great, THANK YOU!!!! my fingers THANK YOU!