Timesheet Formula Identifying Text as Numeric Value

jnm71

New member
Joined
Jun 30, 2014
Messages
3
Reaction score
0
Points
0
Hello, I am creating a timesheet for work. It shows start time, End Time, Break Length and total hours per day. Each heading has a drop down menu, times from 00:00 to 24:00, I can work this formula out =SUM(A2-A1-A3)*24. However this is where I get confused, in the drop down list for times I have also added AL which if selected, should give the value 8 in A3, PAT which if selected should give the value 8 in A3, SICK which if selected, should give the value 0 in A3, AUTH AB which if selected, should give the value 0 in A3, UNAUTH AB which if selected, should give the value 0 in A3 and OTHER which if selected, should give the value 0 in A3. The original hours calculation formula should also be an alternative. Any help would be much appreciated!
 
Try

=IF(OR(A1="AL",A1="PAT"),8,IF(OR(A1="SICK",A1="AUTH AB",A1="UNAUTH AB",A1="OTHER"),0,SUM(A2-A1-A3)*24))
 
That works! Thank you very much. I think I understand the process now. One thing. I have added the correct formula in the add up box underneath the hours. This leaves FALSE in the box unless someone actually is sick etc. Can I hide the initial FALSE statement in the box, and leave it blank unless a SICK, AL, PAT etc is entered into the time sheet. I hope this makes sense!
 
What is that addup formula?
 
Back
Top