Solved

subtract totalled time from a given number of hours - Excel

Posted on 2014-10-01
6
185 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 49

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 32

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

863 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

28 Experts available now in Live!

Get 1:1 Help Now