Accept Military Time without a colon ":"

[DjMikeWatt]

Hi there,

Here is the my situation. I want to enter time into the spreadsheet as military time. Typing 1445 is much faster than 14:45, and it's insanely faster than 2:45 PM. The problem is, that Excel forces me to use the colon on military time (even though military time doesn't actually use a colon). People say, "Excel doesn't know it's a time without the colon", which is complete nonsense because you can format the cell as TIME. That alone tells it that you're entering time.

So... is there a way that I can tell excel to handle cells like this:

I type It displays
0945 9:45 AM
1130 11:30 AM
1400 2:00 PM

But, at the same time, is able to do time calculations on the cells (=C1-A1-30), where A1 is a start time and C1 is an end time (and 30 is just a break being taken out).

This is what I want to do and, while I am completely amazed that this isn't possible by default in Excel, I believe that there is probably a way around it... hoping to find that way here.

THANKS

 

[DjMikeWatt]

The sum is correct, you have already been told that changing the format does not change the value only the appearance.

Change your formulas to the below...


Sheet1

*

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

*

*

*

*

<colgroup><col style="font-weight:bold; width:30px; "><col style="width:152px;"><col style="width:81px;"><col style="width:81px;"><col style="width:62px;"><col style="width:81px;"><col style="width:81px;"><col style="width:62px;"><col style="width:81px;"><col style="width:81px;"><col style="width:62px;"><col style="width:81px;"><col style="width:81px;"><col style="width:62px;"><col style="width:81px;"><col style="width:81px;"><col style="width:62px;"><col style="width:81px;"><col style="width:81px;"><col style="width:62px;"><col style="width:81px;"><col style="width:81px;"><col style="width:41px;"><col style="width:80px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]9[/TD]
[TD="align: center"]Oscar Marrero[/TD]
[TD="align: center"]1:30 PM[/TD]
[TD="align: center"]7:30 PM[/TD]
[TD="align: center"]6:00:00[/TD]

[TD="align: center"]0:00:00[/TD]
[TD="bgcolor: #ccffcc, align: center"]8:00 AM[/TD]
[TD="bgcolor: #ccffcc, align: center"]4:00 PM[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9:00 AM[/TD]
[TD="align: center"]5:30 PM[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]7:30 AM[/TD]
[TD="align: center"]4:00 PM[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]9:00 AM[/TD]
[TD="align: center"]5:30 PM[/TD]
[TD="align: center"]8[/TD]

[TD="align: center"]0[/TD]
[TD="align: center"]38.00[/TD]

</tbody>

Spreadsheet Formulas

Cell Formula D9 =FLOOR(IF(C9-B9>8.5,C9-B9-0.5,C9-B9),1/24) G9 =FLOOR(IF(F9-E9>8.5,F9-E9-0.5,F9-E9),1/24) J9 =FLOOR(IF(I9-H9>8.5,I9-H9-0.5,I9-H9),1/24) M9 =FLOOR(IF(L9-K9>8.5,L9-K9-0.5,L9-K9),1/24) P9 =FLOOR(IF(O9-N9>8.5,O9-N9-0.5,O9-N9),1/24) S9 =FLOOR(IF(R9-Q9>8.5,R9-Q9-0.5,R9-Q9),1/24) V9 =FLOOR(IF(U9-T9>8.5,U9-T9-0.5,U9-T9),1/24) W9 =SUM(D9,G9,J9,M9,P9,S9,V9)*24 <tbody> </tbody>

<tbody>
</tbody>

That's it! Thank you so much! I'm reading up on some of these Excel functions that I wasn't familiar with. I really appreciate everyone's help. This has been a very helpful group. Thank you!