|
Have a look at TimeValue in help.
"Jack Bible" wrote in message ... 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 |
Remove Spaces beginning Time entry
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 |
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" wrote in message ... 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 |
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" wrote in message ... 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 |
Try this:
=TIME(HOUR(TRIM(B11)),,) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jack Bible" wrote in message ... 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 |
Sorry, copied the wrong cell.
Try this: =TIME(HOUR(TRIM(B11)),MROUND(MINUTE(TRIM(B11)),TRI M(I11)),0) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "RagDyeR" wrote in message ... Try this: =TIME(HOUR(TRIM(B11)),,) -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ============================================== "Jack Bible" wrote in message ... 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 |
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" wrote in message ... 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
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" wrote in message ... 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 |
So ... that possibility *doesn't* exist for I11?<g
-- Regards, RD -------------------------------------------------------------------- Please keep all correspondence within the Group, so all may benefit ! -------------------------------------------------------------------- "Jack Bible" wrote in message ... 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" wrote in message ... 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 |
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" wrote in message ... 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" wrote in message ... 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 |
Appreciate the feed-back.
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Jack Bible" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
| All times are GMT +1. The time now is 02:43 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com