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

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

--
Biff
Microsoft Excel MVP


"Dukey" wrote in message
...
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

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.

"Dukey" wrote in message
...
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

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


"Dukey" wrote:

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

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)


On Sat, 14 Feb 2009 23:58:00 -0800, xlmate wrote:

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

HTH

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,

"Chip Pearson" wrote:

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)


On Sat, 14 Feb 2009 23:58:00 -0800, xlmate wrote:

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

HTH

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

mm/dd/yyyy hh:mm

to the cell.

Hope this helps.

Pete

On Feb 16, 4:47*pm, Joe Wildman
wrote:
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,



"Chip Pearson" wrote:
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)

On Sat, 14 Feb 2009 23:58:00 -0800, xlmate wrote:

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

HTH- Hide quoted text -

- Show quoted text -