I have 5 cells in a row (A1 - A5). I need to subtract, in military time, A1
from A2 and then A3 from A4 and the total should be in A5 as standard time.
Cells A1-A4 are formatted as hhmm and cell A5 is formatted as hh:mm.

Simply:

=A2-A1+A4-A3

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Pattio" wrote in message
...
I have 5 cells in a row (A1 - A5). I need to subtract, in military time,
A1
from A2 and then A3 from A4 and the total should be in A5 as standard
time.
Cells A1-A4 are formatted as hhmm and cell A5 is formatted as hh:mm.

I have been trying that but the formula stays in the cell as I typed it
above(not the result but the formula). I also need the end result to be in
standard time, not military time.

"Sandy Mann" wrote:

Simply:

=A2-A1+A4-A3

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Pattio" wrote in message
...
I have 5 cells in a row (A1 - A5). I need to subtract, in military time,
A1
from A2 and then A3 from A4 and the total should be in A5 as standard
time.
Cells A1-A4 are formatted as hhmm and cell A5 is formatted as hh:mm.

Time in Excel is always hh:mm:ssAM/PM no matter how you format it.

hhmm is still hh:mm:ss

hh:mm is still hh:mm:ss

Just do your subtraction in the normal manner as Sandy points out.


Gord Dibben MS Excel MVP

On Sat, 16 Feb 2008 11:35:01 -0800, Pattio
wrote:

I have 5 cells in a row (A1 - A5). I need to subtract, in military time, A1
from A2 and then A3 from A4 and the total should be in A5 as standard time.
Cells A1-A4 are formatted as hhmm and cell A5 is formatted as hh:mm.

I have been trying that but the formula stays in the cell

Which almost certainly means that the cell is formatted as Text.

Re-format the cell as hh:mm and then delete the contents of the cell and
re-enter the formula.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Pattio" wrote in message
...
I have been trying that but the formula stays in the cell as I typed it
above(not the result but the formula). I also need the end result to be
in
standard time, not military time.

"Sandy Mann" wrote:

Simply:

=A2-A1+A4-A3

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Pattio" wrote in message
...
I have 5 cells in a row (A1 - A5). I need to subtract, in military
time,
A1
from A2 and then A3 from A4 and the total should be in A5 as standard
time.
Cells A1-A4 are formatted as hhmm and cell A5 is formatted as hh:mm.

Ooops, sorry, it appears that I changed the cell to TEXT. Now back to the
original problem, I have the problem with A2-4. I have the formula for these
cells set up as hhmm but each time I enter a number, i.e. 0500 it goes back
to 0000. I changed it to hh:mm and it is the same 00:00 but should be 05:00.
What's up?

"Sandy Mann" wrote:

Simply:

=A2-A1+A4-A3

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Pattio" wrote in message
...
I have 5 cells in a row (A1 - A5). I need to subtract, in military time,
A1
from A2 and then A3 from A4 and the total should be in A5 as standard
time.
Cells A1-A4 are formatted as hhmm and cell A5 is formatted as hh:mm.

Formatting as hhmm does not allow you to enter time as 0500

Format as hhmm but enter as 05:00 to get 5:00AM which will be viewed as 0500 in
cell

14:00 to get 2:00PM which will be viewed as 1400 in cell

If you want to enter time as 0500 you will need VBA event code.


Gord

On Sat, 16 Feb 2008 12:19:00 -0800, Pattio
wrote:

Ooops, sorry, it appears that I changed the cell to TEXT. Now back to the
original problem, I have the problem with A2-4. I have the formula for these
cells set up as hhmm but each time I enter a number, i.e. 0500 it goes back
to 0000. I changed it to hh:mm and it is the same 00:00 but should be 05:00.
What's up?

"Sandy Mann" wrote:

Simply:

=A2-A1+A4-A3

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings


Replace @mailinator.com with @tiscali.co.uk


"Pattio" wrote in message
...
I have 5 cells in a row (A1 - A5). I need to subtract, in military time,
A1
from A2 and then A3 from A4 and the total should be in A5 as standard
time.
Cells A1-A4 are formatted as hhmm and cell A5 is formatted as hh:mm.

All of you are great! I have finally gotten it to work. One last question
though. When adding the column to get the total number of hours the result
is strange. I am adding
12:35
10:30
12:10
9:20
10:15
but the total comes out as 6:50 instead of 54:30. I have read the other
threads but can't seem to get the other examples to work. It appears that I
can't go beyond 24 hours.

"Gord Dibben" wrote:

Time in Excel is always hh:mm:ssAM/PM no matter how you format it.

hhmm is still hh:mm:ss

hh:mm is still hh:mm:ss

Just do your subtraction in the normal manner as Sandy points out.


Gord Dibben MS Excel MVP

On Sat, 16 Feb 2008 11:35:01 -0800, Pattio
wrote:

I have 5 cells in a row (A1 - A5). I need to subtract, in military time, A1
from A2 and then A3 from A4 and the total should be in A5 as standard time.
Cells A1-A4 are formatted as hhmm and cell A5 is formatted as hh:mm.

If your total may go beyond 24 hours, format as [h]:mm, not as h:mm

It comes to 54:50, so I'm not sure where your 54:30 comes from?
--
David Biddulph

"Pattio" wrote in message
...
All of you are great! I have finally gotten it to work. One last
question
though. When adding the column to get the total number of hours the
result
is strange. I am adding
12:35
10:30
12:10
9:20
10:15
but the total comes out as 6:50 instead of 54:30. I have read the other
threads but can't seem to get the other examples to work. It appears that
I
can't go beyond 24 hours.

"Gord Dibben" wrote:

Time in Excel is always hh:mm:ssAM/PM no matter how you format it.

hhmm is still hh:mm:ss

hh:mm is still hh:mm:ss

Just do your subtraction in the normal manner as Sandy points out.


Gord Dibben MS Excel MVP

On Sat, 16 Feb 2008 11:35:01 -0800, Pattio

wrote:

I have 5 cells in a row (A1 - A5). I need to subtract, in military
time, A1
from A2 and then A3 from A4 and the total should be in A5 as standard
time.
Cells A1-A4 are formatted as hhmm and cell A5 is formatted as hh:mm.