0

I am trying to create a sheet that can convert shift times into hours worked but will also recognise text values. Been playing around with the IF function and can get it to do one or the other but not both together. The two formulas I have are 

=IF('Shift Times'!C6<=0,"",(TEXT(RIGHT('Shift Times'!C6,4),"00\:00")-TEXT(LEFT('Shift Times'!C6,4),"00\:00"))*24-0.5)
AND
IF('Shift Times'!C6="Sick","Sick"))

Is there a way of combining these two formulas so that they work together? Any help is appreciated.

Improve this question
5
  • 2
    Possible duplicate of Excel - test 3 cells – Máté Juhász May 31 '17 at 11:12
  • if(condition){ do firstthing; }else{ if(othercondition){do secondthing; }else{ do thirdthing; } }. You can use the same logic on Excel functions. – Seth May 31 '17 at 11:39
  • What are the shift times? Is it a single value? I assume it has to be a range. If so, what format is the range? In your code, you're only looking at C6 suggesting there is only a single value. How is this shift timeS – Dave May 31 '17 at 14:29
  • for excel 2016 users (not backward compatible) consider using IFS or SWITCH – gns100 May 31 '17 at 15:16
  • The format of the shift times is: 1000-1730 (for example) so I input that into a worksheet called "shift times" and then have another sheet called "hours worked" which converts the shift time into actual hours worked using the above formula. Does that make more sense? – Stuck Jun 2 '17 at 9:52
1

You can use the AND and OR functions to evaluate multiple possibilities at once.

Use AND in cases where ALL conditions have be true. For example, if you want to enter "Sick" in situations where time is 0 and status is sick, where time is in column C and status is in column D, you could use:

=IF(AND(C6=0,D6="Sick"),"Sick",<calculation>)

Use OR in cases where any one condition has to be true. For example, if column C might have 0 hours or it may have the word "sick" in it, you could use:

=IF(OR(C6<=0,C6="Sick"),"Sick",<calculation>)
Improve this answer
2
  • Thanks, this has helped me get most of the way there. Is there a limit on calculations you make in one formula? – Stuck Jun 2 '17 at 10:05
  • @Stuck You can nest up to seven IF formulae together. If you need more than that, you may need to use a different solution, like a lookup table. – Werrf Jun 2 '17 at 11:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.