Solved

subtract totalled time from a given number of hours - Excel

Posted on 2014-10-01
6
195 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 51

Accepted Solution

by:
Rgonzo1971 earned 100 total points
ID: 40354305
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
ID: 40354327
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 33

Assisted Solution

by:Rob Henson
Rob Henson earned 125 total points
ID: 40354346
Try this:

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

Formatted as [hh]:mm as suggested by RGonzo

Thanks
Rob H
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 14

Assisted Solution

by:frankhelk
frankhelk earned 125 total points
ID: 40354380
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
ID: 40354577
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
ID: 40354580
Got it working myself but the help got me on the right path :-)
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

740 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