Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Time format help needed
I am trying to type in 12:34 as 1234(for example) and have the format convert
it back to 12:34 Is there a way of formatting this conversion? I will need it to be a time format since it is a time card key entry. Thanks -- Danlinksman |
#2
|
|||
|
|||
Hi
you need vBA for this. See: http://www.cpearson.com/excel/DateTimeEntry.htm -- Regards Frank Kabel Frankfurt, Germany "Dan W." schrieb im Newsbeitrag ... I am trying to type in 12:34 as 1234(for example) and have the format convert it back to 12:34 Is there a way of formatting this conversion? I will need it to be a time format since it is a time card key entry. Thanks -- Danlinksman |
#3
|
|||
|
|||
Hi!
An alternative to the VBA solution is to *TEMPORARILY* set AutoCorrect to replace a period "." with the colon ":" and enter the "times" like 12.34. You would have enter the times in a 24 hr clock format but can reformat them after they've all been entered. This is lots faster and easier than using the 1 2 shift colon 3 4 space P M method. Just remember to reset AutoCorrect after you're done. Biff -----Original Message----- I am trying to type in 12:34 as 1234(for example) and have the format convert it back to 12:34 Is there a way of formatting this conversion? I will need it to be a time format since it is a time card key entry. Thanks -- Danlinksman . |
#4
|
|||
|
|||
Format the concerned cells as 0":"00
123 -- 1:23 1234 -- 12:34 However, please note that 5 or more digits number would show as below; 1234567 -- 12345:67 -- Mike "Dan W." wrote in message ... |
#5
|
|||
|
|||
Hi!
Be advised that this method only changes the appearance of the numbers and does not convert them to true times. Using this method the cell looks like 12:34 but the actual underlying value is 1234 (one thousand two hundred thirty- four) Biff -----Original Message----- Format the concerned cells as 0":"00 123 -- 1:23 1234 -- 12:34 However, please note that 5 or more digits number would show as below; 1234567 -- 12345:67 -- Mike "Dan W." wrote in message news:55FB6EF0-E601-4E5E-B631- ... . |
#6
|
|||
|
|||
Another alternative is to use an adjacent cell to do the
conversion for you (this may not be acceptable). You can use a formula thus: =TIMEVALUE(LEFT(D5,2)&":"&RIGHT(D5,2)) where D5 contains your number. |
#7
|
|||
|
|||
Another alternative is to use an adjacent cell to do the
conversion for you (this may not be acceptable). You can use a formula thus: =TIMEVALUE(LEFT(D5,2)&":"&RIGHT(D5,2)) where D5 contains your number. Sorry - I initially posted this as a subthread to a previous reply |
#8
|
|||
|
|||
Saw this on another thread - what a nice, clever person
that Pearson is: http://www.cpearson.com/excel/DateTimeEntry.htm |
#9
|
|||
|
|||
but is it any easier than the 24 hour clock with colon? One shift hardly
does it for me. Bob "Biff" wrote in message ... Hi! An alternative to the VBA solution is to *TEMPORARILY* set AutoCorrect to replace a period "." with the colon ":" and enter the "times" like 12.34. You would have enter the times in a 24 hr clock format but can reformat them after they've all been entered. This is lots faster and easier than using the 1 2 shift colon 3 4 space P M method. Just remember to reset AutoCorrect after you're done. Biff -----Original Message----- I am trying to type in 12:34 as 1234(for example) and have the format convert it back to 12:34 Is there a way of formatting this conversion? I will need it to be a time format since it is a time card key entry. Thanks -- Danlinksman . |
#10
|
|||
|
|||
Can you guys help me with a problem? On an anniversary date, I want to add
vacation days to an individual's vacation accrued cell. How can I do that? "Ben McBen" wrote: Another alternative is to use an adjacent cell to do the conversion for you (this may not be acceptable). You can use a formula thus: =TIMEVALUE(LEFT(D5,2)&":"&RIGHT(D5,2)) where D5 contains your number. |
#11
|
|||
|
|||
but is it any easier than the 24 hour clock with colon?
One shift hardly does it for me. Ah, but that one shift requires two hands! Payroll clerks/timekeepers that either don't want to use the VBA method or don't know about it will really appreciate the difference. Biff -----Original Message----- but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Bob "Biff" wrote in message ... Hi! An alternative to the VBA solution is to *TEMPORARILY* set AutoCorrect to replace a period "." with the colon ":" and enter the "times" like 12.34. You would have enter the times in a 24 hr clock format but can reformat them after they've all been entered. This is lots faster and easier than using the 1 2 shift colon 3 4 space P M method. Just remember to reset AutoCorrect after you're done. Biff -----Original Message----- I am trying to type in 12:34 as 1234(for example) and have the format convert it back to 12:34 Is there a way of formatting this conversion? I will need it to be a time format since it is a time card key entry. Thanks -- Danlinksman . . |
#12
|
|||
|
|||
You may know more payroll clerks than me, but I doubt it, it still requires
coming off of the number keypad that those type of users will use more than perhaps you or I. And if they don't know about Chip's solution, educate them! Bob "Biff" wrote in message ... but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Ah, but that one shift requires two hands! Payroll clerks/timekeepers that either don't want to use the VBA method or don't know about it will really appreciate the difference. Biff -----Original Message----- but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Bob "Biff" wrote in message ... Hi! An alternative to the VBA solution is to *TEMPORARILY* set AutoCorrect to replace a period "." with the colon ":" and enter the "times" like 12.34. You would have enter the times in a 24 hr clock format but can reformat them after they've all been entered. This is lots faster and easier than using the 1 2 shift colon 3 4 space P M method. Just remember to reset AutoCorrect after you're done. Biff -----Original Message----- I am trying to type in 12:34 as 1234(for example) and have the format convert it back to 12:34 Is there a way of formatting this conversion? I will need it to be a time format since it is a time card key entry. Thanks -- Danlinksman . . |
#13
|
|||
|
|||
Oh....
The decimal point IS ON the numeric keypad.... The COLON IS NOT. Biff -----Original Message----- You may know more payroll clerks than me, but I doubt it, it still requires coming off of the number keypad that those type of users will use more than perhaps you or I. And if they don't know about Chip's solution, educate them! Bob "Biff" wrote in message ... but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Ah, but that one shift requires two hands! Payroll clerks/timekeepers that either don't want to use the VBA method or don't know about it will really appreciate the difference. Biff -----Original Message----- but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Bob "Biff" wrote in message ... Hi! An alternative to the VBA solution is to *TEMPORARILY* set AutoCorrect to replace a period "." with the colon ":" and enter the "times" like 12.34. You would have enter the times in a 24 hr clock format but can reformat them after they've all been entered. This is lots faster and easier than using the 1 2 shift colon 3 4 space P M method. Just remember to reset AutoCorrect after you're done. Biff -----Original Message----- I am trying to type in 12:34 as 1234(for example) and have the format convert it back to 12:34 Is there a way of formatting this conversion? I will need it to be a time format since it is a time card key entry. Thanks -- Danlinksman . . . |
#14
|
|||
|
|||
Thank you. I have no problem with military conversion when using hours,
minutes and 10 key.... however the other dept. that are keying want to put time as traditional 24 hour clock with traditional 60 minute /minutes... I like your suggestions. Thanks! "Biff" wrote: Oh.... The decimal point IS ON the numeric keypad.... The COLON IS NOT. Biff -----Original Message----- You may know more payroll clerks than me, but I doubt it, it still requires coming off of the number keypad that those type of users will use more than perhaps you or I. And if they don't know about Chip's solution, educate them! Bob "Biff" wrote in message ... but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Ah, but that one shift requires two hands! Payroll clerks/timekeepers that either don't want to use the VBA method or don't know about it will really appreciate the difference. Biff -----Original Message----- but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Bob "Biff" wrote in message ... Hi! An alternative to the VBA solution is to *TEMPORARILY* set AutoCorrect to replace a period "." with the colon ":" and enter the "times" like 12.34. You would have enter the times in a 24 hr clock format but can reformat them after they've all been entered. This is lots faster and easier than using the 1 2 shift colon 3 4 space P M method. Just remember to reset AutoCorrect after you're done. Biff -----Original Message----- I am trying to type in 12:34 as 1234(for example) and have the format convert it back to 12:34 Is there a way of formatting this conversion? I will need it to be a time format since it is a time card key entry. Thanks -- Danlinksman . . . |
#15
|
|||
|
|||
I have a space limitation I am working with and have no additional colum
room, and the data needs to be visable for our clerical input specialist... Thanks for the suggestion. I will log for future considerations as it may help in future versions. Thanks "jopla" wrote: Can you guys help me with a problem? On an anniversary date, I want to add vacation days to an individual's vacation accrued cell. How can I do that? "Ben McBen" wrote: Another alternative is to use an adjacent cell to do the conversion for you (this may not be acceptable). You can use a formula thus: =TIMEVALUE(LEFT(D5,2)&":"&RIGHT(D5,2)) where D5 contains your number. |
#16
|
|||
|
|||
I wasn't advocating the colon method, just stating that with having to
*TEMPORARILY* set the Autocorrect to replace colon with dot, and still having to enter that character, it's advantage over the colon method is slight, and Chip's solution leaves it trailing in it's wake Bob PS setting autocorrect isn't on the numeric keypad either! "Biff" wrote in message ... Oh.... The decimal point IS ON the numeric keypad.... The COLON IS NOT. Biff -----Original Message----- You may know more payroll clerks than me, but I doubt it, it still requires coming off of the number keypad that those type of users will use more than perhaps you or I. And if they don't know about Chip's solution, educate them! Bob "Biff" wrote in message ... but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Ah, but that one shift requires two hands! Payroll clerks/timekeepers that either don't want to use the VBA method or don't know about it will really appreciate the difference. Biff -----Original Message----- but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Bob "Biff" wrote in message ... Hi! An alternative to the VBA solution is to *TEMPORARILY* set AutoCorrect to replace a period "." with the colon ":" and enter the "times" like 12.34. You would have enter the times in a 24 hr clock format but can reformat them after they've all been entered. This is lots faster and easier than using the 1 2 shift colon 3 4 space P M method. Just remember to reset AutoCorrect after you're done. Biff -----Original Message----- I am trying to type in 12:34 as 1234(for example) and have the format convert it back to 12:34 Is there a way of formatting this conversion? I will need it to be a time format since it is a time card key entry. Thanks -- Danlinksman . . . |
#17
|
|||
|
|||
PS setting autocorrect isn't on the numeric keypad either!
LOL! ....and you won't find an object module there either! <g Biff -----Original Message----- I wasn't advocating the colon method, just stating that with having to *TEMPORARILY* set the Autocorrect to replace colon with dot, and still having to enter that character, it's advantage over the colon method is slight, and Chip's solution leaves it trailing in it's wake Bob PS setting autocorrect isn't on the numeric keypad either! "Biff" wrote in message ... Oh.... The decimal point IS ON the numeric keypad.... The COLON IS NOT. Biff -----Original Message----- You may know more payroll clerks than me, but I doubt it, it still requires coming off of the number keypad that those type of users will use more than perhaps you or I. And if they don't know about Chip's solution, educate them! Bob "Biff" wrote in message ... but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Ah, but that one shift requires two hands! Payroll clerks/timekeepers that either don't want to use the VBA method or don't know about it will really appreciate the difference. Biff -----Original Message----- but is it any easier than the 24 hour clock with colon? One shift hardly does it for me. Bob "Biff" wrote in message ... Hi! An alternative to the VBA solution is to *TEMPORARILY* set AutoCorrect to replace a period "." with the colon ":" and enter the "times" like 12.34. You would have enter the times in a 24 hr clock format but can reformat them after they've all been entered. This is lots faster and easier than using the 1 2 shift colon 3 4 space P M method. Just remember to reset AutoCorrect after you're done. Biff -----Original Message----- I am trying to type in 12:34 as 1234(for example) and have the format convert it back to 12:34 Is there a way of formatting this conversion? I will need it to be a time format since it is a time card key entry. Thanks -- Danlinksman . . . . |
#18
|
|||
|
|||
aah, but you only have to do that once! :-)
Bob "biff" wrote in message ... PS setting autocorrect isn't on the numeric keypad either! LOL! ...and you won't find an object module there either! <g Biff |
#19
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format help needed
If you have more than 4 digits, perhaps you wish to show seconds too, simply
use 00":"00":"00 Now 123456 will show as 12:34:56 You can continue this format for as many digits as you need, just keep repeating the ":"00 "MikeY" wrote: Format the concerned cells as 0":"00 123 -- 1:23 1234 -- 12:34 However, please note that 5 or more digits number would show as below; 1234567 -- 12345:67 -- Mike "Dan W." wrote in message ... |
#20
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Time format help needed
I'm not quite sure why this thread has been revived, having been dormant
since December 2004, but remember the warning given then, that formatting in that way will *NOT* give a number that allows time calculations to be carried out. For example, if you double your 12:34:56 you would get 24:69:12, instead of 25:09:52 -- David Biddulph "TomS" wrote in message ... If you have more than 4 digits, perhaps you wish to show seconds too, simply use 00":"00":"00 Now 123456 will show as 12:34:56 You can continue this format for as many digits as you need, just keep repeating the ":"00 "MikeY" wrote: Format the concerned cells as 0":"00 123 -- 1:23 1234 -- 12:34 However, please note that 5 or more digits number would show as below; 1234567 -- 12345:67 -- Mike "Dan W." wrote in message ... |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
entering numbers to display a time format | Excel Discussion (Misc queries) | |||
adding military time format | Excel Discussion (Misc queries) | |||
How do I format column containing date and time to only show the . | Excel Discussion (Misc queries) | |||
Time Format Auto Entry AM and PM | Excel Discussion (Misc queries) | |||
Subtracting time in date format | Excel Worksheet Functions |