Solved

# subtract totalled time from a given number of hours - Excel

Posted on 2014-10-01
180 Views
so here's my scenario
I've got a list of parts and for each part I need to track time used on them. So far so good. I have then set up a sumifs formula which totals the time used for each part. So far so good.
Now what I want to do is subtract this total time used from a given number of hours so that it shows me the number of hours left for this part. So for the example below. My given number of hours is 2000. My hours used to date is 4:49:00 - and according to this  formula where Calculations!B3 has the 2000 in it (General format)
my hours left are 04:24:00 - which isn't correct obviously
I've tried this:
and got 19:11:00 for my hours left which isn't right either.
So what am I missing here??  Thank you :-)
0
Question by:agwalsh

LVL 48

Accepted Solution

Rgonzo1971 earned 100 total points
HI,

pls try to change the format of the time to

[hh]:mm:ss

EDIT if you want negative Hours to be shown then you have to tick

File / Options / Advanced When calculating this workbook / Use 1904 date system

Regards
0

LVL 19

Assisted Solution

helpfinder earned 150 total points
I am not 100% sure if I get you, but as I understand it your goal (in this example) is to make 2000 - 4:49:00 (where 2000 is in hours).
If so then result should be 1995,18 hours and you can achieve this by formula
=A1-(HOUR(B1)+MINUTE(B1)/60)
where 2000 is in cell A1 and 4:49:00 in cell B1. Also cell where formula is should be formatted as General or Number.

If I am talking about something different as you meant, excuause me.
0

LVL 31

Assisted Solution

Rob Henson earned 125 total points
Try this:

Formatted as [hh]:mm as suggested by RGonzo

Thanks
Rob H
0

LVL 13

Assisted Solution

frankhelk earned 125 total points
Maybe some background info helps:

When doing time calculations, Excel internall represents dates and times as decimal numbers, where a date is the number of days sind 01/01/1900 0:00am, and time values are are stored as fractions of a day - that way today (as Oct 01, 2014, 0:00am) is internally represented as 41913 and the current time (13:57:27) is represented as 0.5815585648, which combines to Now() (01.10.2014 13:57:27) being stored as 41913.5815585648.

With that in mind the calculation of hours is a bit more transparent ... esp. you have to bear in mind that if Excel is forced to format a value as time, it shows only the time fraction and ignores the date part. that way 49 hours have the same appearance than 1 hour or 25. If the date is shown, you'll get a date based on 01/01/1900, which is irritating, too.

If you want to transfer a time into an text showing seconds, minutes, hours, days, months and years correctly, you'll have to split the full days part from the time fraction, break the days part up into the days, moths and years and add the time part as formatted by Excel. That would be a nice exercise for a VBA function .... esp. when the different lengths of a year come into consideration ;-)
0

Author Comment

hi All
Went back to drawing board with all this and got a solution that works..thanks to you all.
I formatted the total hours e.g. 2000 in General. At this point my total hours used are: 04:49:00
Then in the cell where I wanted the answer to appear I did the following:
and formatted this cell as [hh]:mm:ss.
This then gave me the answer of 1995:11:00
which is what I was looking for..so thanks to you all :-)
0

Author Closing Comment

Got it working myself but the help got me on the right path :-)
0

## Featured Post

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…