military time

[Andrea]

I am trying to enter military times in excel 2000
(without having to key : between the hours & minute). I
then need it to calculate the amount of elapsed time
between the two military times input.

I can get it to show the military time with no :, but I
still have to key the time with :.

i.e. - 11:40 PM to 3:00 AM (2340 to 0300)

Is there any way to do this?

 

[JE McGimpsey]

Check out

http://cpearson.com/excel/DateTimeEntry.htm

 

[Jason Morin]

Format the cells as text and then key in the military
times w/out colons. Then you can use:

=TEXT(B1,"00\:00")-TEXT(A1,"00\:00")+(TEXT(B1,"00\:00")
<TEXT(A1,"00\:00"))

assuming the times are in A1 and B1. Format the formula
cell as normal time.

HTH
Jason
Atlanta, GA

 

[Andrea]

Thanks for the help, Jason,but when I put the formula in
the cell & format as regular time the formula
shows "TRUE".

I am trying to calculate response times from the office
to the site & we use military time for this. I can
easily convert the military time to regular time, since
the two columns with the time are hidden. But I need
actual response times for the formula to compute (in
either total minutes or hours & minutes).

Andrea

 

[Andrea]

I went to the web site suggested & don't really
understand what they want me to do. I will look at it
more closely tomorrow & see if I can experiment with it.

Thanks

 

[Jason Morin]

Try:

=TEXT(B1,"00\:00")*1-TEXT(A1,"00\:00")*1+(TEXT(B1,"00
\:00")<TEXT(A1,"00\:00"))*1

Jason

 

[Andrea]

Thanks but this formula shows "FALSE".

Any other thoughts?

Thanks

Andrea

 

[Peo Sjoblom]

Really, I would be very interested to see how that formula can return FALSE?

--

Regards,

Peo Sjoblom

(No private emails please, for everyone's
benefit keep the discussion in the newsgroup/forum)

 

[JE McGimpsey]

Really, I would be very interested to see how that formula can return FALSE?

Format/Cells/Number/Custom "TRUE";"TRUE";"FALSE";@

 

[Fred Smith]

I would do the following:

1. Enter your time as a number (eg, 2340 or 315)
2. Convert the number to an Excel time (=TIME(int(a1/100),mod(a1,100),0)
3. Calculate the time difference between the two (=d1-c1+if(d1<c1,1,0))