Remove Spaces beginning Time entry

J

Jack Bible

I am using "=TIME(HOUR(B11),MROUND(MINUTE(B11),I11),0)" for
a time card, but if a space is entered we get #VALUE error. How can
these spaces be filtered out. Input "06:00" or "06:00 am" works, but "
06:00" gives #VALUE error.

Thanks
Jack
 
P

Peo Sjoblom

Why would anyone put a space in a time value?

=TIME(HOUR(TRIM(B11)),MROUND(MINUTE(TRIM(B11)),I11),0)

--
Regards,

Peo Sjoblom

(No private emails please)
 
B

Bob Phillips

Another way

=TIME(HOUR(SUBSTITUTE(B11," ","")),MROUND(MINUTE(SUBSTITUTE(B11,"
","")),I11),0)

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
R

RagDyeR

Try this:

=TIME(HOUR(TRIM(B11)),,)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I am using "=TIME(HOUR(B11),MROUND(MINUTE(B11),I11),0)" for
a time card, but if a space is entered we get #VALUE error. How can
these spaces be filtered out. Input "06:00" or "06:00 am" works, but "
06:00" gives #VALUE error.

Thanks
Jack
 
R

RagDyeR

Sorry, copied the wrong cell.
Try this:

=TIME(HOUR(TRIM(B11)),MROUND(MINUTE(TRIM(B11)),TRIM(I11)),0)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


Try this:

=TIME(HOUR(TRIM(B11)),,)
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


I am using "=TIME(HOUR(B11),MROUND(MINUTE(B11),I11),0)" for
a time card, but if a space is entered we get #VALUE error. How can
these spaces be filtered out. Input "06:00" or "06:00 am" works, but "
06:00" gives #VALUE error.

Thanks
Jack
 
J

Jack Bible

Good question, but had some people do it, believe they would put a space to
remove the wrong number.
jack
 
J

Jack Bible

Peo

This works very well.
Thanks
Jack

Peo said:
Why would anyone put a space in a time value?

=TIME(HOUR(TRIM(B11)),MROUND(MINUTE(TRIM(B11)),I11),0)

--
Regards,

Peo Sjoblom

(No private emails please)
Click to expand...
 
R

RagDyeR

So ... that possibility *doesn't* exist for I11?<g>
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

Peo

This works very well.
Thanks
Jack
 
J

Jack Bible

No I11 is fixed at 15 to round time at 15 min. intervals.
Thank You all for your help.

Jack
 
Top