1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Negative Times in Excel

Discussion in 'Business Applications' started by Gogs, Aug 20, 2003.

Thread Status:
Not open for further replies.
Advertisement
  1. Gogs

    Gogs Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    121
    I use a very basic spreadsheet to work out my flexi-time each day. This works fine for positive numbers (ie. when I have worked my normal hours in a day) but when I have a negative amount (eg. when I have worked 10 mins less than my normal day) Excel displays ######## in place of the daily balance. Is there any way I can make Excel display the actual balance even when it is negative.

    Thanks in advance.

    Gogs
     
  2. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    EXCEL displays negative numbers. Be more specific about the formulas or cells involved.

    Usually a ####### (or would that be "an ######") is there because the cell size is too small to display the number. Double click the divider line between that column header and the next one (like between C & D to expand C) and see if your result is there. If so you may just need to reduce the number of displayed zeros or change the format.

    If this is not the problem then fill us in a little more.

    - Castleheart
     
  3. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    If you're using time values, go to Tools/Options, Calculation tab and check 1904 date system. However using this system has it's consequences.

    Another method is to multiply by 1440 and format the cell as a number i.e (b1-a1)*1440
     
  4. Gogs

    Gogs Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    121
    It's definetly not due to cell size (tried that). I'll try to explain a bit better.

    Column A has the Date (in date format)
    Column B has Morning start time (in HH:mm:ss format)
    Column C has Morning finish time (as above)
    Column D has Afternoon start time (as above)
    Column E has Afternoon finish time (as above)
    Column F has total time worked in the day (as above)
    Column G has the daily balance (as above) which is Column F minus Column I
    Column H is a running total for the week (in HH:mm:ss format)
    Column I shows the amount of normal working time in the day in HH:mm:ss format) usually 7hrs 35mins.

    Where the problem lies is that I do less than 7hrs 35mins it shows ####' in column G instead of a negative amount of hours.
    eg. 06hrs worked (column F) instead of the normal working time 7hrs 35mins (column I) shows ###### in daily balance (column G) instead of -1hr 35mins

    It would be easier if I could post the spreadsheet but I can't post them on this forum.

    Does this clarify it a bit better?

    Gogs
     
  5. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    I am a little confused, so bear with me as I confuse you.

    First: If you have a column that shows how long you worked today " F "
    and a column that gives you the running total " H "
    then what is the "Balance column" G? If it is just to show how much over or under you were - do you need to expressed in h:mm:ss ? Why not a decimal or a " % " ?

    EXCEL looks at time as part of a day - with 24 hours being 1 and all else being a decimal part thereof. ergo - it doesn't understand a "negative part" of a day.

    If using another format isn't really what you want and you NEED to see the you amount you were over or under each day's alloted work time in h/mm/ss.... perhaps this segue:



    Use two columns:

    G for hour/min/second you worked under the day's time
    H for hours/min/sec you worked over the day's time.

    (we have inserted a column here so everything is shuffled to the right and I would now be the running total and J (ould now be the normal work time in a day)

    stay with me now:

    The UNDER formula would be =J-F rendering a postive number if you work less than the alloted day

    The OVER formula would be =F-J rendering a postive number if you work more than the alloted day

    THEN:

    Format those cells using CUSTOM and use this format:

    h:mm:ss_);[white](h:mm:ss)

    This assumes your background is white. That way when you work "UNDER", nothing appears in the" OVER " cell and vice versa

    Try it. it might suit your needs.

    If not fill us in on what you have to have out of column G and maybe there is a better approach.

    Somebody out there might know a way to so just what you want!

    - Castleheart


    PS: if you use this and want to get rid of the Zero's in cells with formulas which are idle and showing 0.00 you can go into options/view and uncheck SHOW ZERO VALUES - or if you need them elsewhere I can show you another way to hide them in just those cells
     
  6. maxflia10

    maxflia10

    Joined:
    Feb 24, 2003
    Messages:
    331
    To display a negative time in Excel, you'll have to use the 1904 Date System. By doing so, your dates in column A will be off by 4 years. The other alternative is to convert the total hours to a number with two decimal places. This can be achieved by multiplying by 24 hours or 1440 minutes, depending on your needs. BTW, why are you using seconds?
     
  7. Gogs

    Gogs Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    121
    Thanks for all your help guys. Sorry for all the confusion.

    I went for the 1904 date option as it was a quick option & I didn't have any dates just days of the week.

    Gogs
     
  8. Gogs

    Gogs Thread Starter

    Joined:
    Jul 28, 2003
    Messages:
    121
    Thanks for all your help guys. Sorry for all the confusion.

    I went for the 1904 date option as it was a quick option & I didn't have any dates just days of the week.

    Gogs
     
  9. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/157613

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice