I have a list of times and I need to subtract 5 hours from each time. Some
are AM and some are PM. What is the best function to use?

One way:

=A1-TIME(5,0,0)

Alternatively, since times in XL are stored as fractional days, you
could use

=A1 - 5/24

which is just a bit more efficient than using the TIME() function.

In article ,
"Erika" wrote:

I have a list of times and I need to subtract 5 hours from each time. Some
are AM and some are PM. What is the best function to use?

I tried the first example you gave =A1-TIME(5,0,0) - however my result is
########## that no matter how wide I make the column it keeps doing that. I
got the same result with your second suggestion. Do I need to select a
different time format?

"JE McGimpsey" wrote:

One way:

=A1-TIME(5,0,0)

Alternatively, since times in XL are stored as fractional days, you
could use

=A1 - 5/24

which is just a bit more efficient than using the TIME() function.

In article ,
"Erika" wrote:

I have a list of times and I need to subtract 5 hours from each time. Some
are AM and some are PM. What is the best function to use?

That is because A1 probably starts at less than 5 hours, and Excel doesn't
like negative time.

What would you like to happen if A1 is less than 5am to start with?

--
HTH

Bob Phillips

"Erika" wrote in message
...
I tried the first example you gave =A1-TIME(5,0,0) - however my result
is
########## that no matter how wide I make the column it keeps doing that.
I
got the same result with your second suggestion. Do I need to select a
different time format?

"JE McGimpsey" wrote:

One way:

=A1-TIME(5,0,0)

Alternatively, since times in XL are stored as fractional days, you
could use

=A1 - 5/24

which is just a bit more efficient than using the TIME() function.

In article ,
"Erika" wrote:

I have a list of times and I need to subtract 5 hours from each time.
Some
are AM and some are PM. What is the best function to use?

Sorry - If you're using the WinXL default 1900 date system, that
indicates that the result is negative, and that date system can't
display negative times.

Instead, use

=MOD(A1-TIME(5,0,0),1)



In article ,
"Erika" wrote:

I tried the first example you gave =A1-TIME(5,0,0) - however my result is
########## that no matter how wide I make the column it keeps doing that. I
got the same result with your second suggestion. Do I need to select a
different time format?

Bingo! That worked great - thank you for your help.

"JE McGimpsey" wrote:

Sorry - If you're using the WinXL default 1900 date system, that
indicates that the result is negative, and that date system can't
display negative times.

Instead, use

=MOD(A1-TIME(5,0,0),1)



In article ,
"Erika" wrote:

I tried the first example you gave =A1-TIME(5,0,0) - however my result is
########## that no matter how wide I make the column it keeps doing that. I
got the same result with your second suggestion. Do I need to select a
different time format?