Time Question

[Steved]

Hello from Steved

I was given the below formula from this newsgroup

A1
B1 =IF(A1<0.5,A1,A1-0.5) formated as h:mm
C1 =IF(B1<>0.5,B1,B1-0.5)Formated as AM/PM

Ok in A1 I type in 13:15,
in B1 it converts it to 1:15, (which is what I require)
in C1 in this case p.m.

My question is using the above if I type in 12:15 in A1
in B1 it returns 0:15 can the formula make it 12:15.
Thankyou

 

[Stunn]

I'm assuming that B1 is for display purposes only...

=IF(HOUR(A1)>12,A1-0.5,A1)

 

[Juan Sanchez]

Steved

B1: =IF(A1<0.542118056,A1,A1-0.5)

I don't understand what you are doing on C1
In order to preserve AM/PM real value you shoul say that
C1 is the same as A1... i.e. C1: =A1

Cheers
Juan

 

[Fred Smith]

Why go to all this work? Why not just format A1 so it displays the time the
way you want (eg, 1:30 PM -- the third entry under Format
Cells>Number>Time>Type)?

 

[Bob Phillips]

SteveD, Try

=IF(A1<13/24,A1,A1-0.5)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Steved]

Hello Juan from Steved

Juan if I type in A1 15:55 ,with your formula
it allows me to in B1 to convert to 3:55
and in C1 looking in A1 it will return p.m.
as you are aware If I just type in 3:55 it automatically
assumes it is a.m. hence your formula gets around this
problem so the end result from 15:15 is 3:55 p.m and if I
type in A1 3:55 it will put in B1 3:55 and in C1 a.m
the end result in this case will be 3:55 a.m as i dont
want the am/pm displayed in A1, hopefully if you do the
above example you will see where i am coming from.
I wanted to display time in 2 colums time in Col B and
a.m./p.m in Col C.
And once again Thankyou.

 

[Steved]

Hello Stunn yes B1 as you rightfully point out
and thankyou for your formula.

 

[Steved]

Thanks Bob.

-----Original Message-----
SteveD, Try

=IF(A1<13/24,A1,A1-0.5)

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

displays the time
the


.

 

[Steved]

Hello Fred from Steved

Fred I input Data in to a database
So because I receive this information off site I needed
to allow them to type in 1355 they type in up to 400 rows
of time. The 1355 is converted to 13:55 (hh:mm)because
the database is set up with a field for time and a field
for a.m./p.m.

Now all I do is what I see I type hence the reason for
the 2 Columns in Excel.

Thanks for your thoughts. Common sense tells me that's
what I should be doing, but when somebody else designed
this database I just used a work around.