| Home |
| Search |
| Today's Posts |
|
#1
|
|||
|
|||
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 |
|
#2
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 |
|
#4
|
|||
|
|||
|
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 |
|
#5
|
|||
|
|||
|
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 |
|
#6
|
|||
|
|||
|
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 |
|
#7
|
|||
|
|||
|
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 |
|
#8
|
|||
|
|||
|
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 |
|
#9
|
|||
|
|||
|
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 |
|
#10
|
|||
|
|||
|
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 |
|
#11
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Time entry | Excel Discussion (Misc queries) | |||
| entering numbers to display a time format | Excel Discussion (Misc queries) | |||
| Help - Information with time and date | Excel Discussion (Misc queries) | |||
| remove all Hyperlinks at one time. | Excel Discussion (Misc queries) | |||
| Time Format Auto Entry AM and PM | Excel Discussion (Misc queries) | |||
Linear Mode
