Remove Spaces beginning Time entry

[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

[Damon Longworth]

Have a look at TimeValue in help.

"Jack Bible" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[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)


"Jack Bible" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[Jack Bible]

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

Peo Sjoblom wrote:

> 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)
>
> "Jack Bible" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

[Jack Bible]

Peo

This works very well.
Thanks
Jack

Peo Sjoblom wrote:

> 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)
>
> "Jack Bible" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

[RagDyeR]

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

Regards,

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

"Jack Bible" <[email protected]> wrote in message
news:[email protected]...
Peo

This works very well.
Thanks
Jack

Peo Sjoblom wrote:

> 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)
>
> "Jack Bible" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> >

[Jack Bible]

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

Jack

RagDyeR wrote:

> So ... that possibility *doesn't* exist for I11?<g>
> --
>
> Regards,
>
> RD
> --------------------------------------------------------------------
> Please keep all correspondence within the Group, so all may benefit !
> --------------------------------------------------------------------
>
> "Jack Bible" <[email protected]> wrote in message
> news:[email protected]...
> Peo
>
> This works very well.
> Thanks
> Jack
>
> Peo Sjoblom wrote:
>
> > 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)
> >
> > "Jack Bible" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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
> > >

[Ragdyer]

Appreciate the feed-back.
--
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Jack Bible" <[email protected]> wrote in message
news:[email protected]...
> No I11 is fixed at 15 to round time at 15 min. intervals.
> Thank You all for your help.
>
> Jack
>
> RagDyeR wrote:
>
> > So ... that possibility *doesn't* exist for I11?<g>
> > --
> >
> > Regards,
> >
> > RD
> > --------------------------------------------------------------------
> > Please keep all correspondence within the Group, so all may benefit !
> > --------------------------------------------------------------------
> >
> > "Jack Bible" <[email protected]> wrote in message
> > news:[email protected]...
> > Peo
> >
> > This works very well.
> > Thanks
> > Jack
> >
> > Peo Sjoblom wrote:
> >
> > > 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)
> > >
> > > "Jack Bible" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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
> > > >
>

[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)


"Jack Bible" <[email protected]> wrote in message
news:[email protected]...
> 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
>

[RagDyeR]

Try this:

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

HTH,

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


"Jack Bible" <[email protected]> wrote in message
news:[email protected]...
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

[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!
==============================================


"RagDyeR" <[email protected]> wrote in message
news:%[email protected]...
Try this:

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

HTH,

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


"Jack Bible" <[email protected]> wrote in message
news:[email protected]...
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