Hi everybody,
I have a question relating Time function. I have put time in cell A1 and
formula
"=TIME(HOUR(A1)+12,MINUTE(A1),SECOND(A1))" in cell B1 the problem is when
the hour exceeds 12 O'clock midnight it gives the error #NUM! instead of
showing the exact time suppose cell A1 has the value 4.00 PM then the formula
shows the error msg instead of showing 4.00 AM of next day

When I tested your formula in B1,
with A1 containing: 4:00 PM
(not "4.00 PM" as posted - the colon is important)
B1 returned: 4:00 AM
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Arup C" wrote:
Hi everybody,
I have a question relating Time function. I have put time in cell A1 and
formula
"=TIME(HOUR(A1)+12,MINUTE(A1),SECOND(A1))" in cell B1 the problem is when
the hour exceeds 12 O'clock midnight it gives the error #NUM! instead of
showing the exact time suppose cell A1 has the value 4.00 PM then the formula
shows the error msg instead of showing 4.00 AM of next day

Hi Max,
Thanks for replying. It was my mistake. It was now solved but when I am
deducting 12 hours from the given time it is showing the same error.

"Max" wrote:

When I tested your formula in B1,
with A1 containing: 4:00 PM
(not "4.00 PM" as posted - the colon is important)
B1 returned: 4:00 AM
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Arup C" wrote:
Hi everybody,
I have a question relating Time function. I have put time in cell A1 and
formula
"=TIME(HOUR(A1)+12,MINUTE(A1),SECOND(A1))" in cell B1 the problem is when
the hour exceeds 12 O'clock midnight it gives the error #NUM! instead of
showing the exact time suppose cell A1 has the value 4.00 PM then the formula
shows the error msg instead of showing 4.00 AM of next day

TIME won't accept a negative number for hours. You were right the first
time in that you ought to *add* 12 hours, not subtract 12 hours.
--
David Biddulph

"Arup C" wrote in message
...
Hi Max,
Thanks for replying. It was my mistake. It was now solved but when I am
deducting 12 hours from the given time it is showing the same error.

"Max" wrote:

When I tested your formula in B1,
with A1 containing: 4:00 PM
(not "4.00 PM" as posted - the colon is important)
B1 returned: 4:00 AM
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Arup C" wrote:
Hi everybody,
I have a question relating Time function. I have put time in cell A1
and
formula
"=TIME(HOUR(A1)+12,MINUTE(A1),SECOND(A1))" in cell B1 the problem is
when
the hour exceeds 12 O'clock midnight it gives the error #NUM! instead
of
showing the exact time suppose cell A1 has the value 4.00 PM then the
formula
shows the error msg instead of showing 4.00 AM of next day

Hi David,
I think ur right but is there any way out using other function with time
function

"David Biddulph" wrote:

TIME won't accept a negative number for hours. You were right the first
time in that you ought to *add* 12 hours, not subtract 12 hours.
--
David Biddulph

"Arup C" wrote in message
...
Hi Max,
Thanks for replying. It was my mistake. It was now solved but when I am
deducting 12 hours from the given time it is showing the same error.

"Max" wrote:

When I tested your formula in B1,
with A1 containing: 4:00 PM
(not "4.00 PM" as posted - the colon is important)
B1 returned: 4:00 AM
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Arup C" wrote:
Hi everybody,
I have a question relating Time function. I have put time in cell A1
and
formula
"=TIME(HOUR(A1)+12,MINUTE(A1),SECOND(A1))" in cell B1 the problem is
when
the hour exceeds 12 O'clock midnight it gives the error #NUM! instead
of
showing the exact time suppose cell A1 has the value 4.00 PM then the
formula
shows the error msg instead of showing 4.00 AM of next day

Sorry, I don't understand what you are asking.

If you want another way of getting a time equivalent to12 hours before or
after A1, instead of the formula you've got already
[=TIME(HOUR(A1)+12,MINUTE(A1),SECOND(A1)) ],
then you could use =MOD(A1+0.5,1) or =MOD(A1-0.5,1) [both of which would
give the same result], and format the result as time.
Another couple of ways of getting the same result would be
=MOD(A1+TIME(12,0,0),1) or =MOD(A1-TIME(12,0,0),1)

The reason that all these give the same answer is that Excel stores dates
and times as units of one day, so 12 hours is equivalent to 0.5

The MOD(...,1) strips off the date part (the integer component) and leaves
you with the time.
--
David Biddulph

"Arup C" wrote in message
...
Hi David,
I think ur right but is there any way out using other function with time
function

"David Biddulph" wrote:

TIME won't accept a negative number for hours. You were right the first
time in that you ought to *add* 12 hours, not subtract 12 hours.
--
David Biddulph

"Arup C" wrote in message
...
Hi Max,
Thanks for replying. It was my mistake. It was now solved but when I am
deducting 12 hours from the given time it is showing the same error.

"Max" wrote:

When I tested your formula in B1,
with A1 containing: 4:00 PM
(not "4.00 PM" as posted - the colon is important)
B1 returned: 4:00 AM
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Arup C" wrote:
Hi everybody,
I have a question relating Time function. I have put time in cell A1
and
formula
"=TIME(HOUR(A1)+12,MINUTE(A1),SECOND(A1))" in cell B1 the problem is
when
the hour exceeds 12 O'clock midnight it gives the error #NUM!
instead
of
showing the exact time suppose cell A1 has the value 4.00 PM then
the
formula
shows the error msg instead of showing 4.00 AM of next day