Results 1 to 6 of 6

Thread: How to get a negative time value?

  1. #1
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016

    Exclamation How to get a negative time value?



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey everyone!

    I have this spreadsheet that track when I get into to work and when I leave. The "Diff" row is where I want to insert a formula that will take the total amount of time that I've worked in the day and subtract 8 and half hours from it. So for example, under "10/16" I want the formula to output -30 or -.5 as the Total hours worked that day was less than 8 and half (8 hours worked that day).

    I thought this would be simple but apparently you can't show a negative value in Time format which is fine but when you use a Number format the number is between 0 and .9999 so I can't interpret how many hours or minutes I've worked less or extra in that day.

    Feel like this is a simple solution but I'm blanking on this hard for some reason. Can anyone help?

    Thanks in advance!

    Timesheet.xlsx

  2. #2
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    39
    Articles
    0
    Excel Version
    2016
    "10:30 AM" in then "6:30 AM" out by time machine ???

  3. #3
    Acolyte caabyyc's Avatar
    Join Date
    Dec 2014
    Location
    Burnaby/Calgary
    Posts
    39
    Articles
    0
    Excel Version
    2016
    For the same date (use D16 as example, following formula will show you different time in "hour" , if you want to see result in "minute", times 60)
    if IN time is earlier than OUT time
    D16=(D15-D14)*24-8.5

    if IN time is later than OUT time , ask yourself that how it happen .
    D16=(D15-D14)*24-8.5+IF(D15<D14,12,0)
    Last edited by caabyyc; 2019-11-01 at 12:08 AM.

  4. #4
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,705
    Articles
    0
    Excel Version
    2010 on Xubuntu
    Thank you Ken for this secure forum.

  5. #5
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016
    Ah should be 6:30 p.m.

  6. #6
    Seeker gjohal's Avatar
    Join Date
    Nov 2018
    Posts
    15
    Articles
    0
    Excel Version
    2016
    Thanks!

Tags for this Thread

Posting Permissions

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