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.
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
Error value present in cell causing formulas to be negated.
Answers
"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
=A1/2400=IFERROR(A1/2400,0)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.
Discussion
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.