Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Negative time Calculations (2003 SP2)

    Good evening

    I am trying to make a basic 'timesheet' workbook for my drivers but I am having trouble calculating with times, I have managed to get some calculations working whereby people may start before midnight and finish after but I am have trouble in showing whether the driver worked overtime hours or did not work enough hours in a week.

    If you look at the attched workbook you will see that I have tried to calculate the expected hours worked against the actual hours worked, if for instance it is 5 hours more a simple 5 or 5.00 would suffice and on the other hand if they worked 5 hours less than their shift I would like it to show -5 or -5.00, I have tried all of the time, custom, number and general options but cannot seem to achieve what I want.

    Cheers

    Steve
    Attached Files Attached Files
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Negative time Calculations (2003 SP2)

    E10-"D10" doesn't make sense - you're trying to subtract the text "D10" from the time value in E10. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    You can use the simple formula

    =24*(D10-E10)

    in E11, and set the number format of this cell to Number with 2 decimal places. If somebody works 2 hours and 30 minutes overtime, you'll see 2.50, and if somebody works 1 hour and 20 minutes too short, you'll see -1.33.

    If you need to see the time in the format h:mm, use the formula

    =ABS(D10-E10)

    and format as time. You'll have to use another cell to indicate whether this is + or -, for example by using the formula

    =IF(D10<0,"-","+")

  3. #3
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative time Calculations (2003 SP2)

    Thanks Hans

    I thought I had changed the cell back to time, thanks for you examples I am off to play with them now, I did (amazingly) find a work around similar to your first suggestion, I made another column and entered =(D5-E5)*24 and then totalled it in F10 but what took you seconds took me hours!! I like the idea of the time format though, thanks as usual

    Cheers

    Steve
    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

  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: Negative time Calculations (2003 SP2)

    Another option than those suggested that worked in older versions of XL (I don;t have XL2003) is to set under tools-options to the 1904 date system. This will allow displaying directly the negative times...

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative time Calculations (2003 SP2)

    Thanks Steve

    I actually ran across an article some time ago about this and it said something about using extreme caution when using it because in caused some time anomalies, I cannot remember if it was 4 days 1 hour (doesn't MS Excel think that 1900 was a leap year? perhaps thats it) or 1 day 4 hours, but I will take another look

    Thanks for the suggestion

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

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

    Re: Negative time Calculations (2003 SP2)

    Existing dates will change (by 4 years and 1 day) if you turn on the 1904 date system, but only in the active workbook. The 1904 setting is stored in the workbook, it won't affect other workbooks.

  7. #7
    5 Star Lounger
    Join Date
    Aug 2001
    Location
    Surrey, United Kingdom
    Posts
    1,001
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Negative time Calculations (2003 SP2)

    Thanks for the info Hans

    Steve
    Cheers

    Steve

    Asking the questions everbody wants the answers too but feels too stupid to ask themselves :-)

Posting Permissions

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