Solved

subtract totalled time from a given number of hours - Excel

Posted on 2014-10-01
6
180 Views
Last Modified: 2014-10-01
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 :-)
0
Comment
Question by:agwalsh
6 Comments
 
LVL 48

Accepted Solution

by:
Rgonzo1971 earned 100 total points
Comment Utility
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

by:helpfinder
helpfinder earned 150 total points
Comment Utility
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

by:Rob Henson
Rob Henson earned 125 total points
Comment Utility
Try this:

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

Formatted as [hh]:mm as suggested by RGonzo

Thanks
Rob H
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 13

Assisted Solution

by:frankhelk
frankhelk earned 125 total points
Comment Utility
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

by:agwalsh
Comment Utility
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
 

Author Closing Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now