How do I subtract time?? For example

A1=20:00 HRS
B1=15:00 HRS
C1=(A1-B1)-1

I want to always subtract 1:00 hour from the result. , =(A1-B1)-1 I get,
######
How can I get 4:00 as the answer??

Time is stored internally by Excel as fractions of a 24-hour day, so
do this:

=A1-B1-1/24

Hope this helps.

Pete

On Jan 29, 12:42*am, GEM wrote:
How do I subtract time?? For example

A1=20:00 HRS
B1=15:00 HRS
C1=(A1-B1)-1

I want to always subtract 1:00 hour from the result. , =(A1-B1)-1 I get,
######
How can I get 4:00 as the answer??

The question is how are the times entered - are the enter as 15:00 meaning 15
hours or 3 PM?

If you subtract 20 from 15 you get a negative number and then you subtract
another 1, more negative. If the cell where the formula is is formatted to
time you will get #####. Excel does not handle times as negative.


--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"GEM" wrote:

How do I subtract time?? For example

A1=20:00 HRS
B1=15:00 HRS
C1=(A1-B1)-1

I want to always subtract 1:00 hour from the result. , =(A1-B1)-1 I get,
######
How can I get 4:00 as the answer??

My appologies for the last email I read B1-A1.

In your case the problem is the -1. 1 represents an entire day. whereas 1
hour should be entered as =1/24

My comments about Excel handling negative numbers are still correct.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"GEM" wrote:

How do I subtract time?? For example

A1=20:00 HRS
B1=15:00 HRS
C1=(A1-B1)-1

I want to always subtract 1:00 hour from the result. , =(A1-B1)-1 I get,
######
How can I get 4:00 as the answer??

Except when the workbook uses the 1904 date system.

Shane Devenshire wrote:

My appologies for the last email I read B1-A1.

In your case the problem is the -1. 1 represents an entire day. whereas 1
hour should be entered as =1/24

My comments about Excel handling negative numbers are still correct.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

"GEM" wrote:

How do I subtract time?? For example

A1=20:00 HRS
B1=15:00 HRS
C1=(A1-B1)-1

I want to always subtract 1:00 hour from the result. , =(A1-B1)-1 I get,
######
How can I get 4:00 as the answer??

--

Dave Peterson

Perfect, it worked!! Thanks!!!

"Shane Devenshire" wrote:

My appologies for the last email I read B1-A1.

In your case the problem is the -1. 1 represents an entire day. whereas 1
hour should be entered as =1/24

My comments about Excel handling negative numbers are still correct.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"GEM" wrote:

How do I subtract time?? For example

A1=20:00 HRS
B1=15:00 HRS
C1=(A1-B1)-1

I want to always subtract 1:00 hour from the result. , =(A1-B1)-1 I get,
######
How can I get 4:00 as the answer??

One mo

=a1-b1-time(1,0,0)



GEM wrote:

How do I subtract time?? For example

A1=20:00 HRS
B1=15:00 HRS
C1=(A1-B1)-1

I want to always subtract 1:00 hour from the result. , =(A1-B1)-1 I get,
######
How can I get 4:00 as the answer??

--

Dave Peterson