agwalsh
asked on
subtract totalled time from a given number of hours - Excel
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)
=components[@[lifetime (hours)]]-(Calculations!B3
my hours left are 04:24:00 - which isn't correct obviously
I've tried this:
=components[@[lifetime (hours)]]-(Calculations!B3
and got 19:11:00 for my hours left which isn't right either.
So what am I missing here?? Thank you :-)
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)
=components[@[lifetime (hours)]]-(Calculations!B3
my hours left are 04:24:00 - which isn't correct obviously
I've tried this:
=components[@[lifetime (hours)]]-(Calculations!B3
and got 19:11:00 for my hours left which isn't right either.
So what am I missing here?? Thank you :-)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Got it working myself but the help got me on the right path :-)
ASKER
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:
=(components[@[lifetime (hours)]]/24)-(Calculation
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 :-)