**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.*