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*24)
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 :-)
LVL 1
agwalshAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rgonzo1971Commented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
helpfinderIT ConsultantCommented:
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
Rob HensonFinance AnalystCommented:
Try this:

=(Calculations!B3/24) - components[@[lifetime (hours)]]

Formatted as [hh]:mm as suggested by RGonzo

Thanks
Rob H
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

frankhelkCommented:
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
agwalshAuthor Commented:
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:
=(components[@[lifetime (hours)]]/24)-(Calculations!B3)
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
agwalshAuthor Commented:
Got it working myself but the help got me on the right path :-)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.