Time function

[Arup C]

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

 

[Max]

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

 

[Arup C]

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.

 

[David Biddulph]

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.

 

[Arup C]

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

 

[David Biddulph]

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.