# Thread: How to get a negative time value?

1. ## How to get a negative time value?

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?

Timesheet.xlsx

2. "10:30 AM" in then "6:30 AM" out by time machine ???

3. 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)

4. Ah should be 6:30 p.m.

5. Thanks!