Calculating Military Time

[Dukey]

I need a formula so when I type in 2:00 PM it gives me 14:00. Or when I
type in 7:00 PM it gives me 19:00. Also need a formula to add the military
time. For example: If I come to work at 08:00 AM and leave at 5:00PM
(17:00 PM) it will give me the amount of hours worked.

Thanks Dukey

 

[T. Valko]

You can enter the time in 24 hr format like this:

h:mm

0:30 = 12:30 AM
7:30 = 7:30 AM
12:00 = 12:00 PM
18:00 = 6:00 PM
23:45 = 11:45 PM

To caculate the hours worked:

A1 = start time
B1 = end time

=MOD(B1-A1,1)

Format as h:mm

 

[Fred Smith]

A time is a time to Excel. It doesn't matter how it's entered or how it's
formatted. It's still the same time. For your first question, format the
cell as:
hh:mm
You can now enter 2:00 PM or 14:00 and the cell will always display military
time.

For your second question, times are just numbers to Excel, so you do regular
arithmetic with them. In your case, it's:
=a2-a1

Format the way you want (like he:mm)

Regards,
Fred.

 

[xlmate]

1st answer : try using the format hh:mm
2nd answer : try =ABS(B2-A2).

HTH
--
Pls provide your feedback by clicking the Yes button below if this post have
help you. This will help others to search the archives for result better.


Thank You

cheers, francis

 

[Chip Pearson]

2nd answer : try =ABS(B2-A2).

That will give an incorrect result if B2 is less than A2, as in start
at 22:00 and end at 5:00.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)

 

[Joe Wildman]

I have the same problem, I was able to use this code but i am unable to edit
it because it is a formula, =MONTH(NOW()) & "/" & DAY(NOW()) & "/" &
YEAR(NOW()) & " " & HOUR(NOW()) &
":" & MINUTE(NOW())

maybe there is some way to create an input cell as to drop it into the coded
cell as to default to the format needed, my problem is i can't get rid of the
seconds, but also need a way to edit the time,

 

[Pete_UK]

Just put =NOW() in the cell, and apply a custom format of:

mm/dd/yyyy hh:mm

to the cell.

Hope this helps.

Pete