military time


A

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?
 
Ad

Advertisements

J

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
 
A

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
 
A

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
 
J

Jason Morin

Try:

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

Jason
 
Ad

Advertisements

A

Andrea

Thanks but this formula shows "FALSE".

Any other thoughts?

Thanks

Andrea
 
P

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)
 
J

JE McGimpsey

Peo Sjoblom said:
Really, I would be very interested to see how that formula can return FALSE?
Format/Cells/Number/Custom "TRUE";"TRUE";"FALSE";@
 
Ad

Advertisements

F

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Military Time 4
Military Time 2
Military Time 1
Military time 6
Using military time 4
Military time trouble 7
converting military time 6
time calculation with military time 8

Top