+ Reply to Thread
Results 1 to 8 of 8

Entering and calculating hours for a 'timecard'

Hybrid View

  1. #1
    Registered User
    Join Date
    02-07-2014
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    24

    Entering and calculating hours for a 'timecard'

    I'd like to calculate time for a timecard in Excel as a side exercise.
    I'm currently using time in (D6) and time out (E6), both formatting HH:MM AM/PM with a total field reading =MOD(E6-D6,1)*24 formatted as General. This works well.

    The problem is, you have to enter the time as "8:00 AM"
    I've seen this replicated in an excel spreadsheet that allowed shorthand, for example "8 a" instead of "8:00 AM"
    I can't figure out how to get data validation to allow users the option of entering their time in a shorthand format.

    Any ideas?

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,044

    Re: Entering and calculating hours for a 'timecard'

    Hi and welcome to the forum

    if you are just entering hours, you can use 8: enter (8 AM) or 20: enter (8 PM)
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    02-07-2014
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Entering and calculating hours for a 'timecard'

    In this case I woud be working with individuals that are very in their ways, the easiest solution is the accomodate them.

    I am specifically looking for them to enter in 'time punches', using the example D6 and E6 from above. Some users would want to enter some users would use the format "8:00 am" and some would use "8 a". I'm interested in learning how to accomodate this, for my own understanding. I can't seem to figure it out!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,044

    Re: Entering and calculating hours for a 'timecard'

    I dont think there is an easy way out for this. The best option would be to instruct them to enter the data correctly

  5. #5
    Registered User
    Join Date
    02-07-2014
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Entering and calculating hours for a 'timecard'

    I believe you when you say there is no easy way. I'm mostly motivated by my own desire to learn Excel better. I have seen this done before and am curious about how others would recreate it. Unfortunately that example was a protected file, so I can't dissect it.

  6. #6
    Registered User
    Join Date
    02-07-2014
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Entering and calculating hours for a 'timecard'

    It may also help to explain that I am allowing 'codes' to be entered into the initial punch field, as well as timestamps.
    I've included the following picture with what I am trying to accomplish.
    excel.jpg

  7. #7
    Registered User
    Join Date
    02-07-2014
    Location
    California
    MS-Off Ver
    Excel 2010
    Posts
    24

    Re: Entering and calculating hours for a 'timecard'

    Last call for any help, I've searched a lot of Google on this one.

  8. #8
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    53,044

    Re: Entering and calculating hours for a 'timecard'

    Your pic gave me an idea. How about if you use those codes in a drop-down (although you could probably put the times in that drop-down instead?), then, in a hidden column, use a vlookup() to find the times based on that entry selected? You could then use the value returned from the DD, instead of the DD?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Calculating actual hours worked only in core hours
    By Val C in forum Excel General
    Replies: 3
    Last Post: 02-27-2013, 01:54 AM
  2. Timecard to Zero Out Leave Without Pay hours
    By tjeee1993 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 12-29-2012, 06:54 AM
  3. Replies: 7
    Last Post: 07-25-2012, 09:14 AM
  4. Timesheet formula for calculating ordinary hours and overtime hours.
    By zoen in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 12-18-2011, 04:23 AM
  5. Replies: 1
    Last Post: 11-29-2011, 12:19 AM

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