+ Reply to Thread
Results 1 to 7 of 7

Utilising time in a drop down list to show start and finish time

  1. #1
    Registered User
    Join Date
    03-12-2010
    Location
    North Lincolnshire, England
    MS-Off Ver
    Excel 2007
    Posts
    36

    Utilising time in a drop down list to show start and finish time

    Good afternoon

    Sorry if this is an easy fix but i can't get my head around it.

    Basically I have a very simple spreadsheet to be filled in by fairly computer illiterate people and want to produce drop down options for each column to avoid the operators having to type the information in. I have 5 columns
    shift, time , Location, finished, comments/ delays

    Obviously I have completed the list for shift, location & comments but I am trying to work out how to create a drop down list that would enable the operator to choose the correct time without having to trawl through a full list of every minute option on a 24 hour clock, sorry the operation runs 24 hours.

    Is it possible to be able to drop down the list showing the available times or do i need to split the column into 2 and have hours and minutes seperate? which is what I'm also trying to avoid? Cant use auto fill in for exact point the cell is clicked as the sheet is not always filled in real time.

    Hopefully there is a solution however if not i can feel some computer training coming on.

    Thank you in advance
    Last edited by b16dlg; 11-29-2011 at 09:40 AM. Reason: solved

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Utilising time in a drop down list to show start and finish time

    Do you actually need the exact minute, can't you have it rounded to 5 minutes or similar?
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Registered User
    Join Date
    03-12-2010
    Location
    North Lincolnshire, England
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Utilising time in a drop down list to show start and finish time

    It can be rounded up at this stage, however, going forward there will be a requirement that every minute is accounted for correctly hence the reason i'm trying to make the sheet as simple as possible to be filled.

  4. #4
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,202

    Re: Utilising time in a drop down list to show start and finish time

    Presumably you are using Data Validation. Have you got a dummy workbook that you can attach?

  5. #5
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Utilising time in a drop down list to show start and finish time

    hI

    I think the quickest way for the user to enter date is pressing Ctrl + ; (the button right of the L) and for the Time, Ctrl+SHIFT+;
    Now for the solution Validation List, I suggest the following.

    Write to a cell that does not need =NOW(). Make the format that you need.

    After do- Validation-list, in the cell that you need, allowing only data from that cell.

    The trouble is that before the user choose from the list,should have done refresh of time, hitting anything in any cell wants.

    Regards
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  6. #6
    Registered User
    Join Date
    03-12-2010
    Location
    North Lincolnshire, England
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Utilising time in a drop down list to show start and finish time

    Sample sheet
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    03-12-2010
    Location
    North Lincolnshire, England
    MS-Off Ver
    Excel 2007
    Posts
    36

    Re: Utilising time in a drop down list to show start and finish time

    sorry the work sheet attached has got the validation list etc but it has seperate columns for hours and minutes and i'm after getting the hours and minutes (time) in one column sorry for the misleading sheet.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1