Results 1 to 12 of 12
  1. #1
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Formating Times and Calculating Time (2007)

    Attached please find a Time Sheet

    I need the following

    1) To allow users to enter the times for eg 08.00 , 08.15, 13.15 or 0800 0815 1315 and this must then reflect as 8am, 8.15am 13.15am
    2) To calculate the total hours as a whole number
    3) A formula to calculate the total hours -I have tried to set up a formula, but it is not working correctly

    Your assistance will be most appreciated

    Howard
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formating Times and Calculating Time (2007)

    1) See the thread starting at <post:=719,975>post 719,975</post:> for various suggestions on how to enter times.
    2) You can use the following formula in K8 to round hours worked to the nearrest hour:

    =ROUND(24*(IF(H8<E8,H8+1-E8,H8-E8)-(G8-F8)+IF(J8<I8,J8+1-J8,J8-I8)),0)/24

    If all users have the Analysis ToolPak add-in installed, you can also use the MROUND function:

    =MROUND(IF(H8<E8,H8+1-E8,H8-E8)-(G8-F8)+IF(J8<I8,J8+1-J8,J8-I8),1/24)

    3) When I enter some data, the weekly hours appear to be calculated correctly.
    Attached Images Attached Images
    • File Type: png x.png (5.7 KB, 0 views)

  3. #3
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formating Times and Calculating Time (2007)

    Hi Hans

    Thanks for the reply.

    1) I tried to use the codes/suggestions in the post 719,975 to format times , but without any success

    2) I did not want the time rounded to the nearest hour-what i was looking for what the time to be shown as as whole number for eg 7:30 hours to be shown in the total hours as 7.5 as it is seven & a half hours 7:15 to be shown as 7.25 as it is actually seven and a quarter hours etc

    Your assistance in this regard will be most appreciated

    Regards

    Howard

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Formating Times and Calculating Time (2007)

    Excel stores dates and times in units of days, so multiply the days by 24:
    =(IF(H8<E8,H8+1-E8,H8-E8)-(G8-F8)+IF(J8<I8,J8+1-J8,J8-I8))*24

    And then format as a number so there is no conversion to a date or a time...

    Steve

  5. #5
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formating Times and Calculating Time (2007)

    Hi Steve

    Thanks for the help. If all the field are completed formula calculates Hours worked correctly. However, if for eg cell E8 & E9 is completed, then the Hours worked is incorrect.

    It would be appreciated if you could assist in this regard.

    Regards

    Howard
    Attached Files Attached Files

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formating Times and Calculating Time (2007)

    The attached version only calculates worked hours if all fields have been entered.
    The worksheet module contains code derived from Date And Time Entry by Chip Pearson to let the user enter times as 800 or 1630 for 8:00 AM and 4:30 PM.
    The weekly hours have also been formatted as numbers instead of as times.
    Attached Files Attached Files

  7. #7
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formating Times and Calculating Time (2007)

    Hi Hans

    Thanks for the reply. I have now managed to sort out the formatting of the times per the VBA code developed by Chip Pearson http://www.cpearson.com/excel/DateTimeEntry.htm

    It would be appreciated if you would assist in modifying my formula that if only certain of the fields are completed for eg E8 & F8 the correct time worked will be calculated or if time is entered into the starting time only , then a zero will be calculated into Hours worked

    Your assistance will be most appreciated

    Regards

    Howard
    Attached Files Attached Files

  8. #8
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Formating Times and Calculating Time (2007)

    Whilst Hans version works, Mr Rayners may not be too pleased if he does not get paid for part day's.

    The attached version calculates, even if Mr Rayners forgets to return from his tea break. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Attached Files Attached Files

  9. #9
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formating Times and Calculating Time (2007)

    Hi Vegasnath

    Thanks for the help. It works perfectly

    Regards

    Howard

  10. #10
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formating Times and Calculating Time (2007)

    Hi Hans

    The code written by Chip Pearson is great, except the it does not cater for times entered as 2 digits for eg 10, 12, 15 etc if for eg 10 is entered it appears as 12:10 if 12 is entered it appears as 12:12

    It would be appreciated if you would amend the cater to cater for this

    Regards

    Howard

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Formating Times and Calculating Time (2007)

    You can combine Case 1 and Case 2 in the Select Case block as follows:
    <code>
    Case 1, 2 ' e.g., 10 = 10:00 AM
    TimeStr = .Value & ":00"
    </code>
    As a consequence, you won't be able to enter a time such as 12:10 AM by typing 10 or 010 or 0010. You'll have to enter 0:10 and click OK in the error message box.

  12. #12
    Bronze Lounger
    Join Date
    Feb 2008
    Posts
    1,420
    Thanks
    124
    Thanked 5 Times in 5 Posts

    Re: Formating Times and Calculating Time (2007)

    Hi Hans

    Thanks for the help. Muxch appreciated

    Regards


    Howard

Posting Permissions

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