|
User time input
Hopefully I'm posting this in the right area. What I have is a form that
will require the employee to input their start and ending time. Since most do not have a clue as to military time format, I was trying to see if there was a way to either put the time in a pop-up or list. The list or pop-up would show for example 8:00pm but would input it into military time in the cell. yes a newbie. |
User time input
You could create a list of times in AM/PM format, and then link a Data
Validation cell to that list to allow the picking, then show the military time in another cell =--SUBSTITUTE(TEXT(G1,"hh:mm"),":","") -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Renegade40" wrote in message ... Hopefully I'm posting this in the right area. What I have is a form that will require the employee to input their start and ending time. Since most do not have a clue as to military time format, I was trying to see if there was a way to either put the time in a pop-up or list. The list or pop-up would show for example 8:00pm but would input it into military time in the cell. yes a newbie. |
User time input
Hopefully I'm posting this in the right area. What I have is a form that
will require the employee to input their start and ending time. Since most do not have a clue as to military time format, I was trying to see if there was a way to either put the time in a pop-up or list. The list or pop-up would show for example 8:00pm but would input it into military time in the cell. yes a newbie. Why not highlight the entire column, right-click the selection and pick Format Cells from the list that pops up... select Time from the Category list and 13:30 (military time format) from the Type list. Then it won't matter what format the time is entered in, it will show as military time. Rick |
User time input
As I understand it, military time is 1330, not 13:30.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... Hopefully I'm posting this in the right area. What I have is a form that will require the employee to input their start and ending time. Since most do not have a clue as to military time format, I was trying to see if there was a way to either put the time in a pop-up or list. The list or pop-up would show for example 8:00pm but would input it into military time in the cell. yes a newbie. Why not highlight the entire column, right-click the selection and pick Format Cells from the list that pops up... select Time from the Category list and 13:30 (military time format) from the Type list. Then it won't matter what format the time is entered in, it will show as military time. Rick |
User time input
As I understand it, military time is 1330, not 13:30.
Okay, then instead of using Time format with 13:30 as the setting, what about Custom formatting it as hhmm then? That should have the visual effect the OP wants and still allow the user to input time anyway they want. Rick |
User time input
I agree with 'Rick R' and I disagree with 'Bob P'. For my understand it, the
military time is a 24 hrs time clock e.g 10:00 pm, military time is 22:00 "Bob Phillips" wrote: As I understand it, military time is 1330, not 13:30. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... Hopefully I'm posting this in the right area. What I have is a form that will require the employee to input their start and ending time. Since most do not have a clue as to military time format, I was trying to see if there was a way to either put the time in a pop-up or list. The list or pop-up would show for example 8:00pm but would input it into military time in the cell. yes a newbie. Why not highlight the entire column, right-click the selection and pick Format Cells from the list that pops up... select Time from the Category list and 13:30 (military time format) from the Type list. Then it won't matter what format the time is entered in, it will show as military time. Rick |
User time input
Hi
Take a look at http://usmilitary.about.com/od/theor...litarytime.htm -- Regards Roger Govier "Teethless mama" wrote in message ... I agree with 'Rick R' and I disagree with 'Bob P'. For my understand it, the military time is a 24 hrs time clock e.g 10:00 pm, military time is 22:00 "Bob Phillips" wrote: As I understand it, military time is 1330, not 13:30. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Rick Rothstein (MVP - VB)" wrote in message ... Hopefully I'm posting this in the right area. What I have is a form that will require the employee to input their start and ending time. Since most do not have a clue as to military time format, I was trying to see if there was a way to either put the time in a pop-up or list. The list or pop-up would show for example 8:00pm but would input it into military time in the cell. yes a newbie. Why not highlight the entire column, right-click the selection and pick Format Cells from the list that pops up... select Time from the Category list and 13:30 (military time format) from the Type list. Then it won't matter what format the time is entered in, it will show as military time. Rick |
User time input
I'm sorry but I guess I asked the question wrong. let see if I can get this
right this time. There will be 3 columms Let say; A1 Start ,B1 Ending , & C1 total. I understand the military time but the employees (most) do not. what I would like to do is have a pop up or list that would give the employee a set of time that they could click on With out having to know millitary time, sort of like the pop-up calendar or list of sites. I have tried the list part but when I click on 8:00pm it enters the cell as 8:00am even when I change the format. I have even tried the list with one row stating regular time ie 8:00pm and the cell next to it stating 20:00 but it will only but in the first column. Maybe this cannot be done, I don't know.. "Rick Rothstein (MVP - VB)" wrote: Hopefully I'm posting this in the right area. What I have is a form that will require the employee to input their start and ending time. Since most do not have a clue as to military time format, I was trying to see if there was a way to either put the time in a pop-up or list. The list or pop-up would show for example 8:00pm but would input it into military time in the cell. yes a newbie. Why not highlight the entire column, right-click the selection and pick Format Cells from the list that pops up... select Time from the Category list and 13:30 (military time format) from the Type list. Then it won't matter what format the time is entered in, it will show as military time. Rick |
User time input
It works for me with a Validation list (referencing a list of cells)
part but when I click on 8:00pm it enters the cell as 8:00am 8:00pm is not an XL time 8:00 PM is try re-writing the list -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Renegade40" wrote in message ... I'm sorry but I guess I asked the question wrong. let see if I can get this right this time. There will be 3 columms Let say; A1 Start ,B1 Ending , & C1 total. I understand the military time but the employees (most) do not. what I would like to do is have a pop up or list that would give the employee a set of time that they could click on With out having to know millitary time, sort of like the pop-up calendar or list of sites. I have tried the list part but when I click on 8:00pm it enters the cell as 8:00am even when I change the format. I have even tried the list with one row stating regular time ie 8:00pm and the cell next to it stating 20:00 but it will only but in the first column. Maybe this cannot be done, I don't know.. "Rick Rothstein (MVP - VB)" wrote: Hopefully I'm posting this in the right area. What I have is a form that will require the employee to input their start and ending time. Since most do not have a clue as to military time format, I was trying to see if there was a way to either put the time in a pop-up or list. The list or pop-up would show for example 8:00pm but would input it into military time in the cell. yes a newbie. Why not highlight the entire column, right-click the selection and pick Format Cells from the list that pops up... select Time from the Category list and 13:30 (military time format) from the Type list. Then it won't matter what format the time is entered in, it will show as military time. Rick |
User time input
I'm sorry but I guess I asked the question wrong. let see if I can get
this right this time. There will be 3 columms Let say; A1 Start ,B1 Ending , & C1 total. I understand the military time but the employees (most) do not. what I would like to do is have a pop up or list that would give the employee a set of time that they could click on With out having to know millitary time, sort of like the pop-up calendar or list of sites. I have tried the list part but when I click on 8:00pm it enters the cell as 8:00am even when I change the format. I have even tried the list with one row stating regular time ie 8:00pm and the cell next to it stating 20:00 but it will only but in the first column. Maybe this cannot be done, I don't know.. Try this first... click on the "A" in column A and sweep across to column "B" so that both columns are highlighted. Now, right-click in the shaded area and select Format Cells from the menu that pops up. Click on the word "Custom" in the Category list; then click in the Type field and type hhmm in it; then click OK. Now enter your time in one of the cells in either column A or B as a time value (that is, with a colon, you have to use the colon so the field knows it is a time value) using either 12 or 24 hour format and see what ends up being displayed. This way, none of your users needs to know about military time and, since the actual value in the cell is still a time value, you can do straight time calculation on these values). Is that acceptable? Rick |
User time input
Thank you very much, It worked great, I have spent a lot of time trying to
get this to and it was a space that defeated me. Now all I have to do it get it to go past midnight to the next day and I'm set. Thank you again.... "Sandy Mann" wrote: It works for me with a Validation list (referencing a list of cells) part but when I click on 8:00pm it enters the cell as 8:00am 8:00pm is not an XL time 8:00 PM is try re-writing the list -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Renegade40" wrote in message ... I'm sorry but I guess I asked the question wrong. let see if I can get this right this time. There will be 3 columms Let say; A1 Start ,B1 Ending , & C1 total. I understand the military time but the employees (most) do not. what I would like to do is have a pop up or list that would give the employee a set of time that they could click on With out having to know millitary time, sort of like the pop-up calendar or list of sites. I have tried the list part but when I click on 8:00pm it enters the cell as 8:00am even when I change the format. I have even tried the list with one row stating regular time ie 8:00pm and the cell next to it stating 20:00 but it will only but in the first column. Maybe this cannot be done, I don't know.. "Rick Rothstein (MVP - VB)" wrote: Hopefully I'm posting this in the right area. What I have is a form that will require the employee to input their start and ending time. Since most do not have a clue as to military time format, I was trying to see if there was a way to either put the time in a pop-up or list. The list or pop-up would show for example 8:00pm but would input it into military time in the cell. yes a newbie. Why not highlight the entire column, right-click the selection and pick Format Cells from the list that pops up... select Time from the Category list and 13:30 (military time format) from the Type list. Then it won't matter what format the time is entered in, it will show as military time. Rick |
User time input
If you mean calculate the time difference crossing midnight then with the
start time in B2 and the stop time in C2 then use: =C2-B2+(C2<B2) or: =MOD(C2-B2,1) and format as time. If you are adding hours which amount to more then 24 hours then use a ustom format of: "[h]:mm" (without the quotes). This will stop the hours rolling over into day at 24 hours and keep them as hours. -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Renegade40" wrote in message ... Thank you very much, It worked great, I have spent a lot of time trying to get this to and it was a space that defeated me. Now all I have to do it get it to go past midnight to the next day and I'm set. Thank you again.... "Sandy Mann" wrote: It works for me with a Validation list (referencing a list of cells) part but when I click on 8:00pm it enters the cell as 8:00am 8:00pm is not an XL time 8:00 PM is try re-writing the list -- HTH Sandy In Perth, the ancient capital of Scotland and the crowning place of kings Replace @mailinator.com with @tiscali.co.uk "Renegade40" wrote in message ... I'm sorry but I guess I asked the question wrong. let see if I can get this right this time. There will be 3 columms Let say; A1 Start ,B1 Ending , & C1 total. I understand the military time but the employees (most) do not. what I would like to do is have a pop up or list that would give the employee a set of time that they could click on With out having to know millitary time, sort of like the pop-up calendar or list of sites. I have tried the list part but when I click on 8:00pm it enters the cell as 8:00am even when I change the format. I have even tried the list with one row stating regular time ie 8:00pm and the cell next to it stating 20:00 but it will only but in the first column. Maybe this cannot be done, I don't know.. "Rick Rothstein (MVP - VB)" wrote: Hopefully I'm posting this in the right area. What I have is a form that will require the employee to input their start and ending time. Since most do not have a clue as to military time format, I was trying to see if there was a way to either put the time in a pop-up or list. The list or pop-up would show for example 8:00pm but would input it into military time in the cell. yes a newbie. Why not highlight the entire column, right-click the selection and pick Format Cells from the list that pops up... select Time from the Category list and 13:30 (military time format) from the Type list. Then it won't matter what format the time is entered in, it will show as military time. Rick |
| All times are GMT +1. The time now is 11:44 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com