# Excel - IF Function Problem - Expert Solution

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
User 13/06/2018 - 09:19
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
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.

Get instant expert help with Excel and Google Sheets

#### Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Your privacy is guaranteed. Your session will not be used for blog unless you give us persmission.