Error value present in cell causing formulas to be negated.


I created a spreadsheet, designed for payroll scheduling. I've received no formal training in excel, have learned everything I do know through trial and error and internet searches. I will list the series of formulas in generic cell pattern and describe formatting to the best of my ability. Unfortunately,  I don't have a home computer at the moment and this worksheet is on my work computer, so I don't have specific codes at the moment. Here goes...(A1,A2) input number in "military" time format. (A3,A4) convert (A1,A2) values to display actual time in hh:mm. (B1) =sum(A3-A4)converted to numeric decimal (B2) recognizes a greater than number value in(B1) as true. (B3) converts true value in (B2) to ".5" and if "false displays "0".(B4)=Sum(B3-B1). (C1)=(B4). (C2)converts (B2) value to "L". I hope that makes sense to you. The problem I have is (A1,A2) carries the potential of being empty, if (A1,A2) are blank (A3,A4) display #VALUE! Error Symbol and causes the formulas in (B1)(C1)(B4). I've tried the recommended IFERROR,ERROR_TYPE,REPLACE,REPLACEIF,CONVERT, CONVERTIF AND CONDITIONAL FORMATTING formula settings. I can get(A3,A4) to display 12:00am but still interferes with (B1,C1,B4) formula displays.I don't know if I'm using them correctly or In the right order or if the formulas I obtained are correct. PLEASE HELP!!!! I would need a layman's version of the correct formulas and steps to fix this. Thank you in advance for any and all help. 



Selected Answer

"Convert value to L" makes no sense to me, and:

converts true value in (B2) to ".5" and if "false displays "0"

indicates that you don't differentiate between numbers and strings. This should be

=IF(B2=TRUE, 0.5, 0)

Excel loves numbers and is bad with strings. Strings are text, marked by surrounding quotation marks. Text can't be used in multiplications (can't multiply "Yes" with "No").

Now, it seems that you have a string in A1, such as "1100" which is very different from the number 1100. It could also be that you have "" in A1 which is a string (indicated by the quotation marks) and not the same as 0 (zero).

In A3 you should have a formula like

because that would convert your "military time" to 24-hour time (which you could display - but not convert! - to AM/PM). This would cause a #VALUE error if A1 holds a string instead of the expected number, even if the string is "" making you believe that the cell is "empty". It is not (since it contains "") and would cause an error which it would not if its value were 0. So, if you have a formula writing "" to A1, change that formula to write 0 (zero) instead. However, 
would cure that problem.

I suggest you read up on how Excel handles time, and defnitely get rid of all the strings in your calculations. Then you should be just fine.

I wrote about this a few days ago. Take a look.



Thank you so much for your help, I'm very much trying to learn what I can. I will research more the area's you discussed. Is there a specific site I can go to for training that teaches and explains in a more of a "layman's" fashion. I've tried reading instruction guides before but the terminology is very confusing. Also, if you could, what would be the correct method for getting a cell to find either true or .5 value in another cell and display an L in originally selected cell? A now former colleague of mine created formula I'm currently using and I think his version might be wrong. Thanks again so much for your help, it is sincerely appreciated.
KEBinAZ (rep: 4) Sep 14, '17 at 9:40 am
I used your B2 formula and it worked great but for A3 it doesn't work in the function I need it to but I'm sure it's because of all my bad formulas and formatting (which most of them contain " " in the formula). I am going to start from scratch using your advice and equations. In you're comments, you mentioned a formula in A1 but i don't have any formula in there. Do I need one in there for everything to work? For clarification, all formulas and formats were created by an ex-colleage, I've been forced to attempt to finish his format with very little knowledge in excel. If it isn't asking too much. Could you please list the proper formulas for all cell functions listed in original comment? I'm in over my head at the moment and have a deadline to meet so advanced training to understand would not be optimal at the moment. I whole heartedly thank you for all of your help with this.
KEBinAZ (rep: 4) Sep 14, '17 at 3:46 pm
I'm glad you took the courage to start over. You will surely succeed! I shall be glad to help when you get stuck, and so will others on this forum. TeachExcel is a great source for help.
However, I would request that you adhere to both forum rules and forum custom. The custom is that you select an answer that helps you. If the answer that helps you wasn't in response to your own question you can award points. Your own reputation will grow the more answers you accept and points you award.
The forum rules require that you ask only one question in one thread. If you have another question, start another question. Don't expand on questions already asked and answered, and don't ask add-on questions.
With that said, no, there is no need for a formula to be in A1. Just make sure there is no string. Since your worksheet seems to deal with time I think it will be important for you to understand how Excel treats times. You should have no need to show military time and "normal" time or convert the two. In Excel 1 PM is 0.5146667 and you can display that value as 1300 or 13:00 or 01:00 just by changing the cell format, not the cell value.
I hope to have helped oyu and look forward to your next question.
Variatus (rep: 4392) Sep 14, '17 at 9:14 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login