Formula to return value from text

I'm trying to create a schedule that auto generates time based on cell entries. I have everything working until I enter text into a cell to denote PTO, HOL,IN and OFF. My formula will no longer...

excel

Formulas and Functions

OliverScheurich
Apr 25, 2023
Captnshacky
=IFERROR(IF(C10-B10>TIME(5,0,0),C10-B10-TIME(0,30,0),C10-B10),0)+IFERROR(IF(E10-D10>TIME(5,0,0),E10-D10-TIME(0,30,0),E10-D10),0)+IFERROR(IF(G10-F10>TIME(5,0,0),G10-F10-TIME(0,30,0),G10-F10),0)+IFERROR(IF(I10-H10>TIME(5,0,0),I10-H10-TIME(0,30,0),I10-H10),0)+IFERROR(IF(K10-J10>TIME(5,0,0),K10-J10-TIME(0,30,0),K10-J10),0)+IFERROR(IF(M10-L10>TIME(5,0,0),M10-L10-TIME(0,30,0),M10-L10),0)+IFERROR(IF(O10-N10>TIME(5,0,0),O10-N10-TIME(0,30,0),O10-N10),0)

You can try this formula.

OliverScheurich

Gold Contributor

Apr 25, 2023

Captnshacky

=IFERROR(IF(C10-B10>TIME(5,0,0),C10-B10-TIME(0,30,0),C10-B10),0)+IFERROR(IF(E10-D10>TIME(5,0,0),E10-D10-TIME(0,30,0),E10-D10),0)+IFERROR(IF(G10-F10>TIME(5,0,0),G10-F10-TIME(0,30,0),G10-F10),0)+IFERROR(IF(I10-H10>TIME(5,0,0),I10-H10-TIME(0,30,0),I10-H10),0)+IFERROR(IF(K10-J10>TIME(5,0,0),K10-J10-TIME(0,30,0),K10-J10),0)+IFERROR(IF(M10-L10>TIME(5,0,0),M10-L10-TIME(0,30,0),M10-L10),0)+IFERROR(IF(O10-N10>TIME(5,0,0),O10-N10-TIME(0,30,0),O10-N10),0)

You can try this formula.

Captnshacky
Copper Contributor
Apr 25, 2023
OliverScheurich
I wasn't even thinking in that direction.
You rock! Thank you

Forum Discussion

Formula to return value from text

Resources