Question description:
This user has given permission to use the problem statement for this
blog.
I need to total a column of values that are in a custom format h:mm:ss; when you total it in a number format. If I change it to the same customer format
I then need to deduct if from total hours of production.
and does not look right
I am wondering if my total hours worked which is a number value should be changed to look correct
Solved by K. W. in 60 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
13/06/2018 - 08:57
Hi welcome !
User
13/06/2018 - 09:10
hello
User
13/06/2018 - 09:10
I was losing my chats
User
13/06/2018 - 09:10
so a little frustrated
Excelchat Expert
13/06/2018 - 09:10
OK, still here , but we did lose some time
User
13/06/2018 - 09:10
I have a column
User
13/06/2018 - 09:10
in hr:mm: ss format
Excelchat Expert
13/06/2018 - 09:10
LOOKS LIKE 2
User
13/06/2018 - 09:11
and if I total the column and converted to same format .....I get total
User
13/06/2018 - 09:11
My ? is I then want to deduct form Number to team member, x number of hours x number of days
User
13/06/2018 - 09:12
so then my calculation looks strange
Excelchat Expert
13/06/2018 - 09:12
Not able to follow with this screen example?
User
13/06/2018 - 09:13
For instance is I just told at genral format it looks like 39.25 if I change format it looks like 942:00:00
User
13/06/2018 - 09:14
I cannot added to the excel it change is to 6:00:00
User
13/06/2018 - 09:15
are you typing or thinking ?
Excelchat Expert
13/06/2018 - 09:15
The hh:mm:ss is just a format put on top of your result
Excelchat Expert
13/06/2018 - 09:15
If you are subtracting 2 time cells , you can format that result.
User
13/06/2018 - 09:16
hang on
Excelchat Expert
13/06/2018 - 09:16
I assume 39.25 is the sum of sum time calc.
User
13/06/2018 - 09:16
I want to take team members x number of hours x number of days
User
13/06/2018 - 09:16
if I do that it looks like 1056 if I deduct 942:00:00 would that work with 2 different formats?
Excelchat Expert
13/06/2018 - 09:16
Ok 8 X 5= 40
User
13/06/2018 - 09:17
I am doing 6x8x22
User
13/06/2018 - 09:17
six team members x 8 hours x 22 days
User
13/06/2018 - 09:17
to get 1056
User
13/06/2018 - 09:17
so if I take 1056 minus 942:00:00 is that possible?
Excelchat Expert
13/06/2018 - 09:18
That is total hours , no need to apply hh:mm:ss
Excelchat Expert
13/06/2018 - 09:18
that format is for time math only -subtracting one time from another
Excelchat Expert
13/06/2018 - 09:18
Your answer is 1056 hours
User
13/06/2018 - 09:19
that not my answer
Excelchat Expert
13/06/2018 - 09:19
if you want in hours then divide by 60
User
13/06/2018 - 09:19
I am trying to take 1056 number of hours minus the time of 942:00:00
Excelchat Expert
13/06/2018 - 09:19
and remainder can be converted to minutes.
Excelchat Expert
13/06/2018 - 09:19
=1056-942
User
13/06/2018 - 09:20
so take 1056 /60?
User
13/06/2018 - 09:20
I have lost you
Excelchat Expert
13/06/2018 - 09:20
hours remaiing
Excelchat Expert
13/06/2018 - 09:20
what format does the final answer need to be?
Excelchat Expert
13/06/2018 - 09:21
based on this =1056-942
Excelchat Expert
13/06/2018 - 09:21
the answer is a 114 hours. The END
Excelchat Expert
13/06/2018 - 09:21
there is no need or purpose to format any diffrent
User
13/06/2018 - 09:21
I am trying to calculate the time impacted which was 942:00:00 from 1056 hours based on hours and users and days.
Excelchat Expert
13/06/2018 - 09:22
define impacted?
Excelchat Expert
13/06/2018 - 09:22
114 hours difference-
User
13/06/2018 - 09:23
LET me show you on excel what I am getting when I do that
Excelchat Expert
13/06/2018 - 09:23
you mean elapsed days of 6 people working 8 hours?
Excelchat Expert
13/06/2018 - 09:24
What is the purpose of cell B2? where is that coming from?
User
13/06/2018 - 09:24
that how may hours 6x8x22
Excelchat Expert
13/06/2018 - 09:25
That is cell B1
User
13/06/2018 - 09:25
every time I enter 942:00 it changes it to 18:00
User
13/06/2018 - 09:25
yes that cell b1
Excelchat Expert
13/06/2018 - 09:25
Ok, try to format CELL B2 on your sheet to NUMBER
User
13/06/2018 - 09:26
ok that worked
Excelchat Expert
13/06/2018 - 09:27
Great!
User
13/06/2018 - 09:27
so if I had estimated work time of 1056 minus -942.00 and I get 114.00
Excelchat Expert
13/06/2018 - 09:27
Yes
Excelchat Expert
13/06/2018 - 09:27
Our proof is cell C7.
User
13/06/2018 - 09:27
so in English I was only productive 114 hours out of a possible 1056 due to my impacted hours
Excelchat Expert
13/06/2018 - 09:28
If impacted hours mean- non productive, that is correct.
Excelchat Expert
13/06/2018 - 09:29
Like impacted by bad weather, or impacted by a strike or riot. Yes -no productive hours. and only 114 actual working hours.
User
13/06/2018 - 09:29
Ok but if my column in excel show 942:00:00 it does not let me link it my total column it change it back to 39.25
User
13/06/2018 - 09:29
so if I check click -942 it works
User
13/06/2018 - 09:30
that could be a problem if not linked some how
Excelchat Expert
13/06/2018 - 09:31
I would have to see the sheet, but I think this is just a format issue. Make sure the cells are formatted to NUMBER first and only change if need; like for a DATE MM/DD/YYYY.
Excelchat Expert
13/06/2018 - 09:32
=1056-942 = 114. there is no other answer. So check the cell FORMAT.
User
13/06/2018 - 09:32
I agree with the answer;
User
13/06/2018 - 09:32
but if before I had 942:00:00; I had 39.25 which was wrong
User
13/06/2018 - 09:33
but it let me put in another cell equal -M14 which would pick up 39.25
User
13/06/2018 - 09:34
but when I click on 942:00:00 and try to do the same thing it either make is 39.25 then I go enter -M14 and get ###### .
User
13/06/2018 - 09:34
so I go manually enter -942.00 and was trying to link the cell
User
13/06/2018 - 09:34
any suggestions
Excelchat Expert
13/06/2018 - 09:35
That likely means it is formatted for some time display
Excelchat Expert
13/06/2018 - 09:35
FORMAT as a number
User
13/06/2018 - 09:36
then I could get some scenarios like
Excelchat Expert
13/06/2018 - 09:36
Yes there are infinite scenarios with EXCEL
User
13/06/2018 - 09:36
3520 -189.64 which converted would be 3520.00 minus 4551.17:54
User
13/06/2018 - 09:37
the reason I question that is how can I have 4551.17 more than the number of hours worked?
Excelchat Expert
13/06/2018 - 09:38
Not sure
Excelchat Expert
13/06/2018 - 09:38
I would have to see the sheet and understand the context.
User
13/06/2018 - 09:38
it will not let me past values in cell to the right without messing things up
Excelchat Expert
13/06/2018 - 09:39
All I can say for certain is =3520-4551
Excelchat Expert
13/06/2018 - 09:39
=-1031
Excelchat Expert
13/06/2018 - 09:39
What is in the cell to the right?
User
13/06/2018 - 09:40
what you mean?
Excelchat Expert
13/06/2018 - 09:41
What does this mean?
Excelchat Expert
13/06/2018 - 09:41
it will not let me past values in cell to the right without messing things up
User
13/06/2018 - 09:41
the cell displayed on the chat
User
13/06/2018 - 09:41
the spread sheet on our chat
Excelchat Expert
13/06/2018 - 09:41
which cell?
Excelchat Expert
13/06/2018 - 09:42
The view sheet appears to be working?
User
13/06/2018 - 09:43
watch as I type 4551.17
Excelchat Expert
13/06/2018 - 09:43
I would really need to see your sheet to understand exactly what is going on
Excelchat Expert
13/06/2018 - 09:43
ok
User
13/06/2018 - 09:44
what did it change to 8:42:06 , AS I was typeing 4551.17:54
Excelchat Expert
13/06/2018 - 09:45
because it is a date and time cell
Excelchat Expert
13/06/2018 - 09:46
No it is formatted as a NUMBER
User
13/06/2018 - 09:46
so how can I show you what I mean if it changes it
Excelchat Expert
13/06/2018 - 09:46
what do you want in CELL B2 a number or a date?
Excelchat Expert
13/06/2018 - 09:47
Or a formula -same format as number?
User
13/06/2018 - 09:47
I want it to calculate h:mm:ss
Excelchat Expert
13/06/2018 - 09:47
That is not possible with this data
Excelchat Expert
13/06/2018 - 09:47
That is a FORMAT
User
13/06/2018 - 09:48
hmmm?if I have x users x hours x days
Excelchat Expert
13/06/2018 - 09:48
If you subtract 2 dates/time cells, you use HH:MM:SS to format the result
Excelchat Expert
13/06/2018 - 09:48
NO
User
13/06/2018 - 09:48
that value is a number
Excelchat Expert
13/06/2018 - 09:48
That is not what it is for
User
13/06/2018 - 09:49
if I have a number minus the hr,minutes and seconds lost what the best way to show that would showing them the exact hours impacted
Excelchat Expert
13/06/2018 - 09:49
users times hours X days is a single NUMBER
Excelchat Expert
13/06/2018 - 09:50
What is the first number HOURS or a DATE/TIME
Excelchat Expert
13/06/2018 - 09:50
And I thought the =6*8*22 is the number of hours impacted?
User
13/06/2018 - 09:51
10percent x 6users x hours to resolve 160:00 so I then took
User
13/06/2018 - 09:51
number of users x hours it took to resolve issue x percentage of impact
User
13/06/2018 - 09:52
which resulted in 96:00
Excelchat Expert
13/06/2018 - 09:52
.10*6*8=.4.8
Excelchat Expert
13/06/2018 - 09:54
I dont know what numbers you are using for percent of impact or hours to resolve, but you should have a single amount with FORMAT =NUMBER.
Excelchat Expert
13/06/2018 - 09:54
NOT hh:MM:SS
Excelchat Expert
13/06/2018 - 09:55
That is a format for TIME math with result in TIME FORMAT.
Excelchat Expert
13/06/2018 - 09:56
You are working in a Quantity of HOURS. and not TIME.
Excelchat Expert
13/06/2018 - 09:56
8 hours not the same a 8 oclock
Excelchat Expert
13/06/2018 - 09:56
ok
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
