Employer's odd time statement
MZ Tools makes life easier for the Excel VBA coder
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Employer's odd time statement

  1. #1
    New Member
    Join Date
    Feb 2002
    Location
    Inverness, Scotland
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Below is a portion of my employer's time statement. As you can see, the idea is to round up the total to 37.00 hrs for the week. But how on earth do they end up with 7.40 hrs, when the start and end times calculate to a different time in reality? Can anyone figure out the formula, or are they just entering these figures manually!

    Planned Time


    Day Date From To Hours

    Sunday 20/01/2002 00:00 00:00 0.00
    Monday 21/01/2002 08:30 16:54 7.40
    Tuesday 22/01/2002 08:30 16:54 7.40
    Wednesd 23/01/2002 08:30 16:54 7.40
    Thursda 24/01/2002 08:30 16:54 7.40
    Friday 25/01/2002 08:30 16:54 7.40
    Saturda 26/01/2002 00:00 00:00 0.00

    Cheers
    Jake



    [ This Message was edited by: Jakeyboy on 2002-02-18 02:25 ]

  2. #2
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    On 2002-02-18 02:23, Jakeyboy wrote:
    Below is a portion of my employer's time statement. As you can see, the idea is to round up the total to 37.00 hrs for the week. But how on earth do they end up with 7.40 hrs, when the start and end times calculate to a different time in reality? Can anyone figure out the formula, or are they just entering these figures manually!

    Planned Time


    Day Date From To Hours

    Sunday 20/01/2002 00:00 00:00 0.00
    Monday 21/01/2002 08:30 16:54 7.40
    Tuesday 22/01/2002 08:30 16:54 7.40
    Wednesd 23/01/2002 08:30 16:54 7.40
    Thursda 24/01/2002 08:30 16:54 7.40
    Friday 25/01/2002 08:30 16:54 7.40
    Saturda 26/01/2002 00:00 00:00 0.00

    Cheers
    Jake



    [ This Message was edited by: Jakeyboy on 2002-02-18 02:25 ]
    I think they use "a formula" like:

    =((D4-C4)*24)-1

    where C4 is the start time,D4 the end time, and -1 represents the total of breaks (lunch, coffie, etc).

    Enter this formula in column F and format its cell as General, then copy down.

  3. #3
    New Member
    Join Date
    Feb 2002
    Location
    Inverness, Scotland
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Aladin,

    I tried something similar but it doesn't work. What returns is simply -1!?

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    81,803
    Post Thanks / Like
    Mentioned
    12 Post(s)
    Tagged
    1 Thread(s)

    Default

    Jake,

    Lets say that A1:E8 houses your sample data:

    {"Day","Date","From","To","Hours";
    "Sunday","20/01/2002",0,0,0;
    "Monday","21/01/2002",0.354166666666667,0.704166666666667,7.4;
    "Tuesday","22/01/2002",0.354166666666667,0.704166666666667,7.4;
    "Wednesday","23/01/2002",0.354166666666667,0.704166666666667,7.4;
    "Thursday","24/01/2002",0.354166666666667,0.704166666666667,7.4;
    "Friday","25/01/2002",0.354166666666667,0.704166666666667,7.4;
    "Saturday","26/01/2002",0,0,0}

    The strange looking numbers are times in the internal representation that Excel works with.

    Start and end times are formatted as Time with 13:30 as type.

    In E2 enter: =IF(C2,((D2-C2)*24)-1,0)

    Format E2 as General and copy down as far as needed.

    I expanded the formula because Sunday and Saturday entries which are zero (You have apparently applied the shorter version on the zero entries, which will result in -1). The expanded version will produce the intended result also wrt zero entries.

    If intersested in a WB that shows the workings of the formula, just drop me a line.

    Aladin

  5. #5
    Guest

    Default

    I don't see what the big problem is.

    You start at 8:30 am, and work till 4:54 pm.
    That is a total of 8.40 hours. I guess u take an hour long lunch (unpaid), subtracting which gives you 7.4 hours!!

    Hence you work 7 hours and 24 minutes per day, which totals to exactly 37.00 hours per week!!


  6. #6
    New Member
    Join Date
    Feb 2002
    Location
    Inverness, Scotland
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Anonymous,

    The portion of the statement is cosmetic, as I am salaried. The times are not true, as we don't get a lunch break as such. They've just put these hours there to reflect the "normal" 37 hr week that we get paid for. As you rightly state, the times add up to 8.40, if we had an unpaid lunch break it would be correct, but we don't, and the query was really just one of curiosity as to how they came up with those figures. I'll ask payroll, but I doubt if even they know. It's not important anyhow. For your information, I don't work those hours at all - I work nightshift, normally from 6:45pm to 08:30am!!!!

    [ This Message was edited by: Jakeyboy on 2002-02-18 09:47 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com